MaZy MaZy - 1 year ago 76
MySQL Question

How to make two table condition to update one table

Hello guys I don't get it.

I have two tables


1, null, null, user1, user2


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 := FROM user;
UPDATE post SET post.fromid = @curUserid WHERE post.sender = @curUsername;

Answer Source

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

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

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

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download