CODEWITHSUNDEEP
mysqlsql
aarelovich
aarelovich

Reputation: 5566

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

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.

Upvotes: 0

Views: 52

Answers (3)

aarelovich
aarelovich

Reputation: 5566

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.

Upvotes: 0

Noob-o-tron 5000
Noob-o-tron 5000

Reputation: 165

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);

Upvotes: 0

k4st0r42
k4st0r42

Reputation: 1252

Maybe something like that :

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

Upvotes: 1

Related Questions