MaZy MaZy - 3 months ago 8
MySQL Question

How to make two table condition to update one table

Hello guys I don't get it.

I have two tables

Posts:

id|fromid|toid|receiver|sender
1, null, null, user1, user2


User:

id|username
1, user1
2, user2


As you see fromid and toid has null values. So I wanted fill it with user id but I don't get it.

later should entry look like:

1, 2, 1, user1, user2


I tried this to begin with fromid but did not work.

SELECT @curUsername := user.username, @curUserid := user.id FROM user;
UPDATE post SET post.fromid = @curUserid WHERE post.sender = @curUsername;

Answer

When you want to write any expression that combines columns from more than one row, you can use a join.

UPDATE Posts
JOIN User AS FromUser ON Posts.sender = FromUser.username
JOIN User AS ToUser ON Posts.receiver = ToUser.username
SET Posts.fromid = FromUser.id,
    Posts.toid = ToUser.id;

MySQL supports joins in an UPDATE statement (although this is not part of standard SQL).

Comments