MySQL update if the record does not exist in another table

Question

I am trying to find out if it is possible to check if a particular entry exists in table B. If so, do not update table A.

I tried a Google search, but I just found the insert version, and I'm not sure if this is possible with an update request.

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, into which I add a new field in the child table with the condition that this value does not exist in the column of the main table.

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

The meaning of the parameters and tables:

• t1: child table
• t2: main table
• 1st ?: The value to be set in the t1.c1 field if the condition is met
• 2 ?: The identifier of the record in which the updated field is located.
• 3 ?: The value needed to evaluate the condition when I compare it with the field t2

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

When using this operator, when we refer to the field of the main table, the column must have a primary key. That is why the resemblance to a foreign key.

---

The second option:

 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 must be the same . Where column is the column of the main table.

On the other hand, when using this other sentence, we have more flexibility, since this is not a necessary condition for the column of the main table to have a primary key.

Answer 3

You can also try using the exists expression. Using the existing syntax for older versions of MySQL, since K. 5.0, turned out to be faster for me than using a nested query.

 update table_a set some_column = 'bla' where not exists (select id from table_b where table_a.id = table_b.id)