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

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 retur开发者_如何学JAVAns the ORA-01427 error, Please can anyone correct my query ?

---

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;