Updating table column from a subquery

Question

I am trying to update a table column based off a subquery from the same table. The table has columns Name Id and Target Id. I am trying to take a subset of name and Id and populate column Target Id. For Example, I am tring to get all the id whose value is 2 and populate all the Target Id that has name XXX and YYY in this example and so

  Name    ID   Target Id
  XXX      13
  XXX      29 
  XXX      31
  YYY      17
  YYY      29 
  YYY      38

Result trying to get:

  Name    ID   Target Id
  XXX      13       29
  XXX      29       29
  XXX      31       29
  YYY      17       29
  YYY      29       29
  YYY      38       29

I have tried this statement

UPDATE tableA a LEFT JOIN (SELECT name, id 
FROM tableA 
WHERE = '2') b
on a.Name = b.Name
SET a.Tartget_id = b.Id

I get error in Oracle using TOAD : ORA-00971: missing SET keyword

It's not clear what logic you are using to determine the Target ID value. What is `WHERE = '2'` supposed to mean? What are you trying to compare to 2? — Dave Costa, Sep 05 '19 at 20:55
Im not trying to compare 2 im trying to find ids that are 2 and populate it in target Id column — AC25, Sep 05 '19 at 21:03
`WHERE = '2'` is not valid Oracle syntax. What are you trying to achieve as there are no IDs in your data set with the value of `2`? — MT0, Sep 05 '19 at 21:08
@AC25 none of the values of `ID` in your sample are 2. What do you mean by "ids that are 2"? — Dave Costa, Sep 05 '19 at 23:42

Eray Balkanli · Answer 1 · 2019-09-05T21:04:45.670

0

Try below, valid syntax for SQL Server:

UPDATE a 
SET a.Target_id = b.Id
FROM tableA a
LEFT JOIN (SELECT name, id 
           FROM tableA 
           WHERE myField = '2') b
on a.Name = b.Name

For Oracle, try:

UPDATE (
   select a.Target_Id, b.Id
   from tableA a
   LEFT JOIN (Select name, id from tableA WHERE myField='2') b
   on a.Name=b.Name
) x
SET x.Target_Id = x.Id

Alternatively:

UPDATE tableA a SET a.Target_ID = (SELECT b.Id
                                  FROM tableA b
                                  WHERE a.name = b.Name and myField='2')
WHERE EXISTS (SELECT a2.CODE
              FROM tableA a2
              WHERE a1.name = a2.name);

Reference: Update statement with inner join on Oracle

edited Sep 05 '19 at 21:04

answered Sep 05 '19 at 20:53

Eray Balkanli

7,752
11
48
82

I don't think this is valid Oracle syntax. – Dave Costa Sep 05 '19 at 20:54
@DaveCosta thanks for the warning, thought it is an SQL Server related question. Is my oracle syntax correct now you think? – Eray Balkanli Sep 05 '19 at 20:59
Could you please check my alternative solution @AC25 – Eray Balkanli Sep 05 '19 at 21:05
@AC25 make sure you are updating "myField" with your column name – Eray Balkanli Sep 05 '19 at 21:05

MT0 · Answer 2 · 2019-09-05T21:20:30.773

Use a MERGE statement.

Oracle Setup:

CREATE TABLE TableA (
  Name VARCHAR2(3),
  ID   NUMBER,
  Target_Id NUMBER
);

INSERT INTO TableA ( Name, Id )
  SELECT 'XXX', 13 FROM DUAL UNION ALL
  SELECT 'XXX', 29 FROM DUAL UNION ALL 
  SELECT 'XXX', 31 FROM DUAL UNION ALL
  SELECT 'YYY', 17 FROM DUAL UNION ALL
  SELECT 'YYY', 29 FROM DUAL UNION ALL
  SELECT 'YYY', 38 FROM DUAL;

Merge:

MERGE INTO tableA dst
USING tableA src 
ON ( dst.Name = src.Name )
WHEN MATCHED THEN
  UPDATE SET Target_id = src.Id
  WHERE src.id = 29;

Output:

SELECT * FROM tableA

NAME | ID | TARGET_ID
:--- | -: | --------:
XXX  | 13 |        29
XXX  | 29 |        29
XXX  | 31 |        29
YYY  | 17 |        29
YYY  | 29 |        29
YYY  | 38 |        29

db<>fiddle here

Or, if you are trying to update rows where there are duplicate IDs:

MERGE INTO tableA dst
USING (
  SELECT t.*,
         COUNT(*) OVER ( PARTITION BY id ) AS count_ids
  FROM   tableA t
) src 
ON ( dst.Name = src.Name AND count_ids = 2 )
WHEN MATCHED THEN
  UPDATE SET Target_id = src.Id;

Updating table column from a subquery

2 Answers2