2

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 ?

krtek
  • 26,334
  • 5
  • 56
  • 84
sailaja
  • 379
  • 6
  • 15
  • 26
  • you should provide the name of the database server you're using. Depending on it, better answer can be provided ! – krtek Sep 22 '11 at 03:39
  • Given the error number, I'd say this is Oracle. (Better tagging would still have been nice, though.) – John Flatness Sep 22 '11 at 03:41
  • If this is Oracle, my answer won't work at all... You can have a look at : http://stackoverflow.com/questions/2446764/oracle-update-statement-with-inner-join – krtek Sep 22 '11 at 03:44
  • 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 Sep 27 '11 at 04:11

1 Answers1

2

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;
krtek
  • 26,334
  • 5
  • 56
  • 84
  • Like said in my comment to your question, if you're using Oracle, this won't work, better follow the link I provided. – krtek Sep 22 '11 at 03:46