UPDATE FROM…Oracle and MSSQL Examples

UPDATE FROM
** SQL version ***
UPDATE erp.rse
SET expardesc = r.expardesc || ‘ – ‘ || p.parname
FROM erp.rse r
INNER JOIN erp.omqc_paramdesc p ON p.rsid = r.rsid
AND r.rtype = ‘PSVP’

*** Oracle vesrion ***
UPDATE erp.rse
SET rse.expardesc = rse.expardesc || ‘ – ‘ || (SELECT p.parname FROM erp.omqc_paramdesc p WHERE p.exparid = rse.exparid)
WHERE EXISTS (SELECT p.* FROM erp.omqc_paramdesc p WHERE p.exparid = rse.exparid)
AND rse.rtype = ‘PSVP’

*** ORACLE EXAMPLE ***
update gdb84edit.fire_symbol_tracking
set uniq_id = (select location_id from fsts.fsts_locations where fsts_locations.bldg_num = fire_symbol_tracking.bldg_num)
where exists (select fsts_locations.* from fsts.fsts_locations where fsts_locations.bldg_num = fire_symbol_tracking.bldg_num)
/

*** ORACLE EXAMPLE TO UPDATE MULTIPLE COLUMNS ***
UPDATE erp.tosvalue2 t1
SET (t1.tsdavg,t1.tsdmin,t1.tsdmax,t1.tsdn) = (SELECT AVG(t2.tsdelta),MIN(t2.tsdelta),MAX(t2.tsdelta),COUNT(t2.tsdelta) FROM erp.tosvalue2 t2 WHERE t2.LOCID = t1.LOCID GROUP BY t2.LOCID)
WHERE EXISTS (SELECT 1 FROM erp.tosvalue2 t2 WHERE t2.LOCID = t1.LOCID)

*** ORACLE “MERGE” ***
I’ve also seen some people use the Oracle MERGE function, which is worth researching

Tagged: MSSQL, Oracle, SQL Server, UPDATE FROM

Posted in: MSSQL, Oracle, PL/SQL, SQL, SQL Server, T-SQL