MySQL update if record does not exists in other table

Question

I am trying to figure out if it is possible to check if a specific record exists in table B. if so, don't update table A.

I have tried googling, but I only find the insert version and I'm not sure if it's even possible with an update query.

Thanks in advance

Answer 1

update table_to_update 
set some_column = 123
where id = 1
and id not in (select id from table_b)

Answer 2

The following SQL statements function as a foreign key in where I add a new field in the daughter table with the condition that this value does not exist in a column of the master table.

UPDATE t1
SET c1 = ?
WHERE t1.id = ?
AND ? NOT IN (SELECT id FROM t2);

Meaning of the parameters and tables:

• t1: daughter table
• t2: master table
• 1st ? : Value that will be established in the field of t1.c1 if the condition is met
• 2nd ? : Identifier of the record where the field to be updated is located
• 3rd ? : Value necessary to evaluate the condition, where I compare it with the field of t2

The value of the first and third parameters must be the same!

When using this statement, when we refer to the field of the master table, the column must have a primary key. That's why the similarity with the foreign key.

---

The second alternative would also be:

UPDATE t1 a
LEFT JOIN t2 b
ON b.column = ?
SET t1.c1 = ?
WHERE b.column IS NULL && a.id = ?;

The first and second parameters have to be the same. Where column is a column of the master table.

On the other hand, when using this other sentence we have more flexibility, since it is not a necessary condition that the column of the master table has a primary key.