Saturday, April 26, 2008

Update Query with Join in Oracle Database

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:

Anonymous said...

Umm. Thanks. Opened the book, did you? May I refer you to asktom.oracle.com, forums.oracle.com, www.dizwell.com...

Anonymous said...

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

Anonymous said...

Thanks..Its was very useful

Anonymous said...

Nice one! Thanks for the tip.

Unknown said...

This is good one...Appreciated

Muthu said...

could try this one
UPDATE table_a a
SET field_2 = ( SELECT field_2
FROM table_b b
WHERE b.id = a.id )
;

Muthu said...

you can also try this
UPDATE table_a a
SET field_2 = ( SELECT field_2
FROM table_b b
WHERE b.id = a.id )
;

Muthu said...

try this one
UPDATE table_a a
SET field_2 = ( SELECT field_2
FROM table_b b
WHERE b.id = a.id )
;