CODEWITHSUNDEEP
mysqlmariadb
MaZy
MaZy

Reputation: 91

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;

Upvotes: 1

Views: 19

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562310

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

Upvotes: 2

Related Questions