Re: SQL help required for a brain not working too well today

Michael Serbanescu wrote:
>
> Try this:
>
> UPDATE T1 a
> SET a.col1=(SELECT b.newcol1 FROM T2 b WHERE b.UnqKey1=a.UnqKey1),
> col3=(SELECT b.newcol3 FROM T2 b WHERE b.UnqKey1=a.UnqKey1),
> col7=(SELECT b.newcol7 FROM T2 b WHERE b.UnqKey1=a.UnqKey1)
> WHERE a.UnqKey1 IN (SELECT UnqKey1 FROM T2);
>
> Hope this helps.
>
> Michael Serbanescu

This also works and it may even be faster..

UPDATE T1 a
SET (a.col1,a.col3,a.col7)=(SELECT b.newcol1,b.newcol3,b.newcol7 FROM T2 b WHERE b.UnqKey1=a.UnqKey1)
WHERE a.UnqKey1 IN (SELECT UnqKey1 FROM T2);

However, the best way of doing this, in my opinion, would be to write a PL/SQL procedure with 2 cursors..   

Tansel

> -------------------------------------------------------------------------
> Bob Johnson wrote:
> >
> > HELP !!
> >
> > My brain seems to have stopped working.
> >
> > I have a table T1 with the columns UnqKey1, col1, col2, col3, col4,
> > col5, col6, col7. The table contains 1 million rows
> >
> > and I have table T2 with the columns UnqKey1, newcol1, newcol3, newcol7.
> > This table contains 100 rows (of updated info).
> >
> > What is the most efficient way of updating T1 such that col1 is set to
> > newcol1, col3 set to newcol3 and col7 set to newcol7 where the keys
> > match.
> >
> > Thanks in advance, post to the newsgroup only please.
Received on Tue Feb 24 1998 - 00:00:00 CST