ORA-01427: single-row subquery returns more than one row update...?? Help?

2
votes

My Query returns this error ORA-01427: single-row subquery returns more than one row update, This is my query

Update Table_b B
Set B.Material_Desc = (Select A.Material_Desc From Table_a A Where A.PartNo = B.PartNo)

I have two different tables : Table_a and Table_b, both have same columns PartNo and Material_Desc. I want the Material_Desc in Table_b to update the Material_Desc in Table_a when PartNo are equals.

The above query returns the ORA-01427 error, Please can anyone correct my query ?

1
sql
you should provide the name of the database server you're using. Depending on it, better answer can be provided ! – krtek
Given the error number, I'd say this is Oracle. (Better tagging would still have been nice, though.) – John Flatness
If this is Oracle, my answer won't work at all... You can have a look at : stackoverflow.com/questions/2446764/… – krtek
MERGE INTO Table_b USING ( SELECT t1.rowid AS rid, t2.Material_Desc FROM Table_b t1 JOIN Table_a t2 ON Table_b.PartNo = Table_a.PartNo ) ON rowid = rid WHEN MATCHED THEN UPDATE SET Table_a.Material_Desc = Table_b.Material_Desc, I just got this query from above link, This is giving me ORA-00969: missing ON keyword error, Please help me. – sailaja

1 Answers

2
votes

The problem is your subquery is returning a whole bunch of rows where you should have only one. You can't do this like this.

Depending on the SQL database you're using, something like this should work better : 

UPDATE Table_b B
SET B.Materiel_Desc = A.Materiel_Desc
INNER JOIN Table_a A ON A.PartNo = B.PartNo

It is possible you must adapt the syntax to your database. For example, I think you cannot do it like this with MySQL. According to http://dev.mysql.com/doc/refman/5.0/en/update.html you should do :

UPDATE Table_b, Table_A
SET Table_b.Materiel_Desc = Table_A.Materiel_Desc
WHERE Table_b.PartNo = Table_a.PartNo;