Re: Update Query Question

Travis Rogers <trdrdriror_at_home.com> wrote in message news:37BF5EE8.81EDD2_at_home.com...
> 2 Tables Master and Temp.
>
> Temp has 2 fields...
> temp_id NUMBER(10)
> temp_update VARCHAR2(8)
> The Primary key for Temp is a field called temp_id.
> temp has approx 350k rows
>
> Master has approx 50 fields but the only 2 of importance to this
> question are...
> ID NUMBER(10)
> clid VARCHAR2(8)
> The Primary key for Master is a field called ID.
> Master has approx 17million rows
>
> I wanted to update Master with temp by "joining" on Master.ID and
> Temp.temp_id.
>
> The sytax I was given is...
>
> UPDATE master a
> SET a.clid = (SELECT b.temp_update
> FROM temp b
> WHERE a.id = b.temp_id and b.temp_other is not null);
>
> This took forever and completely filled a 2 gig rollback segment.
>
> What is the correct syntax for this situation?

Try this:

update (select m.clid, t.temp_update from master m, temp t where m.id=t.temp_id)

    set clid=temp_update; Received on Sun Aug 22 1999 - 14:53:14 CDT