MySQL - UPDATE query based on SELECT Query

Question

I need to check (from the same table) if there is an association between two events based on date-time.

One set of data will contain the ending date-time of certain events and the other set of data will contain the starting date-time for other events.

If the first event completes before the second event then I would like to link them up.

What I have so far is:

SELECT name as name_A, date-time as end_DTS, id as id_A  FROM tableA WHERE criteria = 1   SELECT name as name_B, date-time as start_DTS, id as id_B  FROM tableA WHERE criteria = 2 

Then I join them:

SELECT name_A, name_B, id_A, id_B,  if(start_DTS > end_DTS,'VALID','') as validation_check FROM tableA LEFT JOIN tableB ON name_A = name_B 

Can I then, based on my validation_check field, run a UPDATE query with the SELECT nested?

Answer 1

You can actually do this one of two ways:

MySQL update join syntax:

UPDATE tableA a INNER JOIN tableB b ON a.name_a = b.name_b SET validation_check = if(start_dts > end_dts, 'VALID', '') -- where clause can go here 

ANSI SQL syntax:

UPDATE tableA SET validation_check =      (SELECT if(start_DTS > end_DTS, 'VALID', '') AS validation_check         FROM tableA         INNER JOIN tableB ON name_A = name_B         WHERE id_A = tableA.id_A) 

Pick whichever one seems most natural to you.