CODEWITHSUNDEEP
mysqlsqljoinsql-update
Phil
Phil

Reputation: 1849

How to update column if value is not present in other table

I have a table board with unique column id and column hide. I have another table pins with a column board_id which is not unique. Every pins.board_id is also in board.id, and there can be multiple pins.board_id's that are the same.

Now I need to check every board.id to see whether it is present in any pins.board_id, and if not set hide to '1'. I imagine the way to do this is a join, but doesn't a join just match up where there IS the same value in both tables?

Any advice appreciated!

Upvotes: 0

Views: 774

Answers (1)

John Woo
John Woo

Reputation: 263893

Join both tables using LEFT JOIN. The column that contains null value on board_id simply means that it doesn't exist.

UPDATE  board a
        LEFT JOIN pins b
            ON a.id = b.board_ID
SET     a.hide = 1
WHERE   b.board_ID IS NULL

Upvotes: 1

Related Questions