How to update the values of a single column with the result of a query?

Question

I'm having trouble getting this query to work in mysql:

UPDATE proyects_c 
   SET director=(SELECT users.keyid 
                   FROM users,proyects 
                  WHERE users.username=proyects.director
                );

Now the problem is that the subquery returns more than one row. Thing is, this is what I want. The number of rows that it returns is the same as the number of rows in proyects_c, hence what I expected this to do is update every row in the column director with the result of the query.

However I get the error:

ERROR 1242 (21000) at line 23: Subquery returns more than 1 row

Which makes sense, but I can't get it to do what I want. What am I doing wrong?

As a secondary question, how can I split this into two queries? For clarity's sake.

What if users.keyid returns multiple values? ERROR itself is saying that. — AK47, Oct 16 '14 at 10:06
Check this. http://stackoverflow.com/questions/15209414/mysql-update-join — Aravindhan, Oct 16 '14 at 10:14
Do you have a primary key in `proyects_c`? or anything to vinculate every row to `proyects`? — Oscar Pérez, Oct 16 '14 at 10:22
What I wanted to do is copy the values from from the query result to each of the rows (the relation being the position, that is the first result to the first row, second result to the second row and so forth) to each of the rows in the column director — aarelovich, Oct 16 '14 at 12:27

score 1 · Answer 1 · answered Oct 16 '14 at 10:11

1

Maybe something like that :

update proyects_c p
inner join users u on
    u.username = p.director
set p.director = u.keyid

answered Oct 16 '14 at 10:11

k4st0r42

1,174
1
15
29

score 0 · Answer 2 · answered Oct 16 '14 at 10:21

You need to specify "Top 1" in the select in T-SQL, or "LIMIT 1" (if memory serves) in MySQL to only return one row.

Try (assuming T-SQL):

UPDATE proyects_c SET director=(SELECT TOP 1 users.keyid FROM users,proyects WHERE users.username=proyects.director);

score 0 · Answer 3 · answered Oct 16 '14 at 12:32

I did something to bypass the problem. The idea was given to me by Oscar Pérez. What I did is added a column to proyects_c (admin_id). Once that was done, this is the query I used:

UPDATE proyects_c, users SET proyects_c.admin_id=users.keyid WHERE proyects_c.director=users.username;

This did what I wanted. After that I deleted the director column and renamed admin_id to director. What I wanted to do is change the type of director from a string to an int in and add the int value from user.keyid corresponding the name (string) that used to be in director. So this worked for me. Oscar made me realize that the ONLY relation between the row I wanted to modify and the row from the query result was the position. I made it so that the relation was an equal value in two different fields. Thanks for the help.

How to update the values of a single column with the result of a query?

3 Answers3