CODEWITHSUNDEEP
sqloracle-databasejoinsql-update
Nick
Nick

Reputation: 1243

SQL update with joins

I have data table1.fieldToChange that is currently populated from table2.oldData... but I would like to update this and change it to table2.newData

Here's the code I am using to try and achieve this:

UPDATE table1
SET table1.fieldToChange =
  (SELECT table2.newData
  FROM table2
  WHERE table2.oldData = table1.newData
  ) ;

But I get an 'ORA-01427' error.

Both table2.newData and table2.oldData are unique identifiers and only used once in the database. Any help would be much appreciated!

Thank you

Upvotes: 1

Views: 279

Answers (4)

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115530

Don't you mean?

UPDATE table1
SET fieldToChange =
  (SELECT table2.newData
  FROM table2
  WHERE table2.oldData = table1.fieldToChange     ---- and NOT table1.newData ?
  ) ;

Upvotes: 1

mcha
mcha

Reputation: 2998

try it with MERGE statement: 

MERGE INTO table1 tgt
USING (SELECT newData,oldData 
         FROM table2
        WHERE table2.oldData = table1.newData) src
ON (src.oldData = tgt.newData)
WHEN MATCHED THEN
  UPDATE SET tgt.fieldToChange = src.newData;

Upvotes: 1

billinkc
billinkc

Reputation: 61211

The ORA-01427 indicates you are returning more than one value in a subquery. Modify your query to contain a DISTINCT clause or some other mechanism to uniquely identify a row in the subquery.

UPDATE table1
SET table1.fieldToChange =
  (SELECT DISTINCT table2.newData
  FROM table2
  WHERE table2.oldData = table1.newData
  ) ;

Upvotes: 1

Jimmie R. Houts
Jimmie R. Houts

Reputation: 7818

Not sure if this works in Oracle, but in MS SQL you can do this:

UPDATE table1
SET fieldToChange = table2.newData
FROM table2  
WHERE table2.oldData = fieldToChange

Note that you cannot have an alias on the table being updated.

Upvotes: 0

Related Questions