mysql - MySQL-基于SELECT查询的UPDATE查询(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?

(然后，可以基于我的validation_check字段运行带有SELECT嵌套的UPDATE查询吗？)

  ask by John M translate from so

Answer 1

You can actually do this one of two ways:

(您实际上可以通过以下两种方式之一执行此操作：)

MySQL update join syntax:

(MySQL更新联接语法：)

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:

(ANSI SQL语法：)

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.

(选择最适合您的那个。)