Update With A Subquery In Mysql

I have an update query that I need to run in MySQL and am having some trouble with it. I have spent the last hour researching SO for a solution, but couldn't find one that actually

Solution 1:

Well, it sounds like your name field is not unique. Your subquery matches more than one row, so you either need to find a unique id to match on other than name, or else, if you want to just take the first result from the subquery do this:

UPDATE TABLE1 SET ID = (SELECT TABLE2.ID FROM TABLE2, TABLE1 WHERE TABLE1.NAME=TABLE2.NAME LIMIT 1) WHERE TABLE1.ID IS NULL

Solution 2:

UPDATE TABLE1, TABLE2 
  SET TABLE1.ID = TABLE2.ID
  WHERE TABLE1.ID ISNULLAND TABLE1.NAME = TABLE2.NAME

should probably do what you want, assuming that NAME is unique accross TABLE1 and TABLE2 for all names.

Solution 3:

SELECT TABLE2.ID FROM TABLE2, TABLE1 WHERE TABLE1.NAME=TABLE2.NAME

this query returning more than one row

Your Query:UPDATE TABLE1 SETID= ( // here setting one value



TABLE1.NAME=TABLE2.NAME //  more than one matched records are available

Here you are setting id,But when sub query returns more than one row it can not set one value