MySQL - UPDATE query based on SELECT Query

Question

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

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

If the first event gets completed before the second event then I want to link them up.

What I am having 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

So based on my validation_check field, can I then run an UPDATE query with the SELECT nested?

Answer 1

You could 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)

Choose whichever one appears most natural to you.

Want to be a SQL expert? Enroll at Intellipaat in this SQL Certification course.

If you want to know more about MySQL, refer to the below MySQL tutorial video:

Answer 2

Query: 

WITH EndEvents AS ( 

    SELECT name AS name_A, date_time AS end_DTS, id AS id_A 

    FROM tableA 

    WHERE criteria = 1), 

StartEvents AS ( 

    SELECT name AS name_B, date_time AS start_DTS, id AS id_B 

    FROM tableA 

    WHERE criteria = 2) 

SELECT e.name_A, s.name_B, e.id_A, s.id_B, 

   CASE  

        WHEN s.start_DTS > e.end_DTS THEN 'VALID' 

        ELSE '' 

    END AS validation_check 

FROM EndEvents e 

LEFT JOIN StartEvents s 

ON e.name_A = s.name_B 

 

 

Answer 3

You can perform an UPDATE based on the results of your SELECT query by utilizing a self-join with conditions that match your requirements. In this case, since you want to update records only if the start_DTS is after the end_DTS, you’ll need to construct an UPDATE query that joins the table to itself and applies the necessary date conditions.

Here’s an example of how you could structure the query:

UPDATE tableA AS A

JOIN tableA AS B

ON A.name = B.name

AND A.criteria = 1

AND B.criteria = 2

AND B.start_DTS > A.end_DTS

SET A.linked_id = B.id;

Explanation:

Self-Join on the Same Table: Here, we’re joining tableA to itself, giving each side an alias (A and B) to differentiate the sets of data.

Applying the Filtering Criteria: We let A denote the rows where criteria = 1 (the end date-times) and B where criteria = 2 (the start date-times).

Setting the Date-Time Condition: The condition B.start_DTS > A.end_DTS only includes records in which B's start date-time is after A's end date-time.

Linking the Records: The query updates A.linked_id (presuming this column is intended to store a reference to the associated id of B) with B.id where the conditions are met.

This will link tableA records by updating linked_id with the id of matching entries, where appropriate. It’s always a good idea to back up your data before running the UPDATE to prevent accidental changes in case of unexpected matches.