CODEWITHSUNDEEP
mysql
red
red

Reputation: 1619

Update two tables using a single query

I found in StackOverflow a solution that fits my needs but when I use this solution only the first table is updated:

Here is my query:

UPDATE posts_flags
    INNER JOIN
    posts ON (posts_flags.content_id = posts.id )
SET 
    posts.published = 5,
    posts_flags.status = 'new_state'
WHERE posts.id = ?;

Only the posts.published being updated, also if the query runs without any error the posts_flags.status not change.

What's wrong? Thanks

UPDATE

Thank you all, really strange, now it's working, so weird

Upvotes: 0

Views: 37

Answers (1)

P.Salmon
P.Salmon

Reputation: 17615

Th query looks fine - here's the proof

drop table if exists posts,posts_flags;

create table posts(id int,published int);
create table posts_flags(content_id int,status varchar(20));

insert into posts values(1,1);
insert into posts_flags values(1,null);

UPDATE posts_flags
    INNER JOIN
    posts ON (posts_flags.content_id = posts.id )
SET 
    posts.published = 5,
    posts_flags.status = 'new_state'
WHERE posts.id = 1;

select * 
from posts
join posts_flags on posts_flags.content_id = posts.id;

+------+-----------+------------+-----------+
| id   | published | content_id | status    |
+------+-----------+------------+-----------+
|    1 |         5 |          1 | new_state |
+------+-----------+------------+-----------+
1 row in set (0.00 sec)

If your model differs then please add table definitions and sample data as text to the question.

Upvotes: 2

Related Questions