niceguy niceguy - 3 months ago 18
SQL Question

How do you use an update between two tables in Oracle SQL

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:

UPDATE gm_prc
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'
                  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).