niceguy - 2 months ago 11

SQL Question

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