問題描述

我需要檢查(從同一張表中)基于日期-時間的兩個事件之間是否存在關聯.

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

然后我加入他們:

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

然后，我可以根據我的validation_check 字段運行帶有 SELECT 嵌套的 UPDATE 查詢嗎?

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

推薦答案

您實際上可以通過以下兩種方式之一執行此操作:

You can actually do this one of two ways:

MySQL 更新連接語法:

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 語法:

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.

這篇關于MySQL - 基于 SELECT 查詢的 UPDATE 查詢的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！