CODEWITHSUNDEEP
mysqlmany-to-many
Mohammad Ali Akbari
Mohammad Ali Akbari

Reputation: 10395

MySQL update many-to-many tabel relation

I have tables A and B and A_has_B where have this fields:

A: id, name

B: id, name

A_has_B: a_id, b_id, background

Previously when I had not 'background' field in A_has_B, I did update by delete all record in A_has_B and reinsert updated records.

but now how can I update A_has_B whitout deleteing records that should reinsert?

Upvotes: 0

Views: 380

Answers (2)

symcbean
symcbean

Reputation: 48357

Off the top of my head, I would think something like....

DELETE FROM a_has_b
WHERE NOT EXISTS (
  SELECT 1 FROM b
  WHERE b.id=a_has_b.b_id
);

(and similar for table 'a')

or

DELETE FROM a_has_b
WHERE b.id <> ANY (SELECT id FROM b);

Upvotes: 1

Mike Sherrill &#39;Cat Recall&#39;
Mike Sherrill &#39;Cat Recall&#39;

Reputation: 95532

It sounds like you just need to 

update A_has_B
set background = ???
where a_id = ??? and b_id = ???

You'll have to fill in the missing bits.

Upvotes: 1

Related Questions