潛水珽的異想世界

如果有個table需要根據其他table來異動(更新、刪除)資料,該怎麼做呢?這個時候就會用到所謂的update、delete join,請參考以下這篇文章.
(ref:http://rejeev.blogspot.com/2008/04/update-query-with-join-in-oracle.html)


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)