the below FOR loop doesn't work. I have two columns PID, PAYMENT in table t1 and table t2. I want to update PAYMENT in table t1 from table t2 where t1.PID=t2.PID
FOR X IN(select paymentterm,pid from temp_project)
LOOP
update project p
set p.paymentterm=temp_project.PID
where p.PID=X.PID;
END LOOP;
commit;
You can achieve this behavior without looping:
UPDATE project
SET paymentterm = (SELECT peymentterm
FROM temp_project
WHERE project.pid = temp_project.pid)
WHERE pid IN (SELECT pid FROM temp_project)
WHERE clause. I wish Oracle had a sensible UPDATE JOIN syntax...Try:
update project p
set paymentterm = (select t.paymentterm
from temp_project tp
where tp.pid = p.pid)
where pid in (select pid from temp_project)
... or, if temp_project.pid is constrained to be unique:
update (select p.pid,
p.paymentterm,
t.paymentterm new_paymentterm
from project p join temp_project t on p.pid = t.pid)
set paymentterm = new_paymentterm;
You might make sure that you're not making changes where none are required with:
update (select p.pid,
p.paymentterm,
t.paymentterm new_paymentterm
from project p join temp_project t on p.pid = t.pid
where coalesce(p.paymentterm,-1000) != coalesce(t.paymentterm,-1000))
set paymentterm = new_paymentterm;
(Guessing at -1000 being an impossible value for paymentterm there). This could also be written as a MERGE statement.
