Consider the tables
Employee(empid, empname, salary, location)
and the table
Address(addId, houseNo, Street, city, country, empId)
We want to update the location column with city column wherever the location is null.
Update Employee e
SET location = (select city from Address a where e.empid = a.empId)
where location is null
However if you have one million record in Employee table the sub query will execute as many times. It is going to take long.
You can re-write the query as follows:
Update
(Select location ll, city cc from Employee e, Address a where e.empid = A.empId and e.location is null)
SET ll = cc
There are two conditions for above query
1) You can update only columns from one table
2) The second table (that is not being modified) must be key preserved - that is, there can be at most one in second table for each record in first table - that is, there should be a unique constraint in second table for the columns used join condition
Second query is much cheaper than the first one when we have large number of records in both table (or in first table)
8 comments:
Umm. Thanks. Opened the book, did you? May I refer you to asktom.oracle.com, forums.oracle.com, www.dizwell.com...
Very to the point and helpfull, thank you. Please disregard the earlier comment by the chap who did not have the nerve to leave a name
Thanks..Its was very useful
Nice one! Thanks for the tip.
This is good one...Appreciated
could try this one
UPDATE table_a a
SET field_2 = ( SELECT field_2
FROM table_b b
WHERE b.id = a.id )
;
you can also try this
UPDATE table_a a
SET field_2 = ( SELECT field_2
FROM table_b b
WHERE b.id = a.id )
;
try this one
UPDATE table_a a
SET field_2 = ( SELECT field_2
FROM table_b b
WHERE b.id = a.id )
;
Post a Comment