Hopefully this is relatively simple. I want to update a table in Oracle but the data being compared is between two tables. How can I accomplish this? I tried the following SQL but know is incorrect after trying it:
SET gm.RETAIL_DT = '01-JAN-2000'
from gm_prc gm, itm_prc_cmp3 itm
where gm.sku_num = itm.SKU_NUM
and gm.PC_NUM = itm.PC_NUM
The syntax you are using doesn't work for Oracle. But you can do this using standard SQL:
UPDATE gm_prc SET RETAIL_DT = '01-JAN-2000' WHERE EXISTS (SELECT 1 FROM itm_prc_cmp3 itm WHERE gm_prc.sku_num = itm.SKU_NUM AND gm_prc.PC_NUM = itm.PC_NUM );
This should work in both Oracle and SQL Server (which is what your syntax suggests for the original query).