CODEWITHSUNDEEP
mysql
Ian Atkin
Ian Atkin

Reputation: 6346

UPDATE multiple rows in a MySQL table based on id existence in another table

I would normally do this programmatically, but I'm in a learning mood today... I looked around SO but didn't find anything exactly like my problem.

I have two tables item, person, and a third that contains associations, item_person. Not everyone has an item so the association doesn't always exist. Now, I want to maintain a status column in the item table which is partly based on whether an item is associated with a person. So...

Table: item
    item_id
    description
    status

Table: person
    person_id
    name

Table: item_person
    item_id
    person_id

Imagines that I want to UPDATE the item status to 1 if an association exists in item_person...

Upvotes: 2

Views: 913

Answers (2)

Michael Fredrickson
Michael Fredrickson

Reputation: 37388

-- If the association exists...
update item i
join item_person ip
    on ip.item_id = i.item_id
set i.status = 1

-- If the association does not exist...
update item i
left join item_person ip
    on ip.item_id = i.item_id
set i.status = 0
where ip.item_id is null

Upvotes: 3

hd1
hd1

Reputation: 34657

  1. UPDATE item set item_status = 1 where exists item_person.item = item.item_id
  2. UPDATE item set item_status = 1 where not exists item_person.item = item.item_id

Note, both queries are untested, as I don't have access to mysql from here and you haven't given a fiddle. Feel free to leave a comment if you need help debugging though.

Upvotes: 0

Related Questions