niceguy niceguy - 1 month ago 7
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

Answer

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).

Comments