0

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 worked. I need to do the following:

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

I have been getting the Error Code: 1242. Subquery returns more than 1 row error. How can I modify my query to make it run successfully?

Basically, I need to fill in the blanks of all values of one column based on a condition, from another table. Please guide me on this issue. Thank you!

Improve this question
0

3 Answers 3

Reset to default
2

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

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Also, you should know that subqueries are inefficient. Most subqueries can be replaced with left joins or other types of joins. Try to avoid subqueries if you can.
I tried this and it runs without any errors, except that it does not change anything. It takes about 2-3 minutes to run on about 1100 rows in table1 and about 150k rows in table2, and returns the number of rows affected and matched and the number of warnings (0), but when I look at table1, nothing has changed. Why could this be?
Try running just the subquery SELECT TABLE2.ID FROM TABLE2, TABLE1 WHERE TABLE1.NAME=TABLE2.NAME LIMIT 1. Perhaps you get results different than you expect from that. Also run the query without the LIMIT 1 just to see what results you're getting. That might give you some clues as to what's going on. As I mentioned, subqueries are inefficient, thus slow. You do have primary keys set up for these tables, yes?
Yes, both the tables have primary keys. I'll try running parts of it and let you know what happens. Thanks for the guidance! :)
1 
UPDATE TABLE1, TABLE2 
  SET TABLE1.ID = TABLE2.ID
  WHERE TABLE1.ID IS NULL
  AND TABLE1.NAME = TABLE2.NAME

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

Improve this answer

4 Comments

I tried this, but it gives me an Unknown column ID in 'IN/ALL/ANY subquery' error. Why is that happening?
The first ID is ambiguous here. SET ID needs to be ` SET TABLE1.ID`. That might fix the error. Why you're getting unknown column error rather than an ambiguous column name error I don't know.
@starshine531 thanks for the help, it was indeed broken. I reverted to my initial answer, and found the typo that was breaking it. see sqlfiddle.com/#!8/ed7df
Yes, this revised query should work. @CodingInCircles I hope this works for you.
0

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

this query returning more than one row

Your Query:UPDATE TABLE1 SET ID = ( // 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

Improve this answer

4 Comments

Is this suppose to be an answer or a question?
@Krister Andersson i explained below the reason.It is an answer not question
@CodingInCircles did you check my answer
Thank you for the comment, but I know why it is happening. What I want is a fix or a workaround. Do you know how to resolve the issue? Thanks!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.