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:
(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)