CODEWITHSUNDEEP
mysqlsql
sfarzoso
sfarzoso

Reputation: 1610

Cannot update specific records using JOIN

I'm trying to update specific competition using the JOIN. The structure of the tables is the following:

country:

id | name | iso
 5  Brazil  BR

competition:

id | country_id | name     |
 1      5          Serie A
 2      5          Serie B
 3      5          Serie C

competition_seasons:

id | competition_id | name  | update_at
 1         1           2019     2019-01-15 00:00:00
 2         1           2018     2019-01-15 00:00:00
 3         1           2017     2019-01-15 00:00:00
 4         2           2019     2019-01-15 00:00:00
 5         2           2018     2019-01-15 00:00:00
 6         2           2017     2019-01-15 00:00:00
 7         3           2019     2019-01-15 00:00:00
 8         3           2018     2019-01-15 00:00:00
 9         3           2017     2019-01-15 00:00:00

The goal is set update_at on competition_seasons to null. So I wrote this query:

UPDATE country n
    JOIN competition c ON n.id = c.country_id
    JOIN competition_seasons s ON c.id = s.competition_id
    SET s.update_at = NULL
    WHERE n.name = "Brazil"

the problem's that the query doesn't update anything. What I did wrong?

Upvotes: 0

Views: 29

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522302

You should list the table you intend to update first in your query. In this case we can reverse the order of the joins, and list the competition_seasons table first:

UPDATE competition_seasons s     -- target table, listed first
INNER JOIN competition c
    ON c.id = s.competition_id
INNER JOIN country n
    ON n.id = c.country_id
SET
    s.update_at = NULL
WHERE
    n.name = 'Brazil';

Note that the order of the joins should not matter here, assuming they are all inner joins.

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1270623

The goal is set update_at on competition_seasons to null.

I would expect

UPDATE competition_seasons cs
    SET update_at = NULL;

Is there some other condition that you are not describing?

Upvotes: 1

Related Questions