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

One set of data will contain the end date time of some events, and the other set of data will contain the start date time of other events.

If the first event completes before the second, then I want to link them.

So far, I have:

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 run an UPDATE query with a SELECT nesting based on my validation [check] field?

#1 building

If someone tries to update data from one database to another, no matter which table they are targeting, there must be some criteria.

This is better and cleaner for all levels:

UPDATE dbname1.content targetTable

LEFT JOIN dbname2.someothertable sourceTable ON
    targetTable.compare_field= sourceTable.compare_field
SET
    targetTable.col1  = sourceTable.cola,
    targetTable.col2 = sourceTable.colb, 
    targetTable.col3 = sourceTable.colc, 
    targetTable.col4 = sourceTable.cold 

Terra! Good results!

Based on the above understanding, you can modify the settings field and the enable condition to work. You can also perform a check, then pull the data into a temporary table, and then use the above syntax to replace the table and column names to run the update.

I hope it works, if I can't know. I will write an exact query for you.

#2 building

You can actually do this in 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 the one that suits you best.

#3 building

UPDATE
    `table1` AS `dest`,
    (
        SELECT
            *
        FROM
            `table2`
        WHERE
            `id` = x
    ) AS `src`
SET
    `dest`.`col1` = `src`.`col1`
WHERE
    `dest`.`id` = x
;

I hope it works for you.

#4 building

UPDATE 
  receipt_invoices dest,
  (
    SELECT 
      `receipt_id`,
      CAST((net * 100) / 112 AS DECIMAL (11, 2)) witoutvat 
    FROM
      receipt 
    WHERE CAST((net * 100) / 112 AS DECIMAL (11, 2)) != total 
      AND vat_percentage = 12
  ) src 
SET
  dest.price = src.witoutvat,
  dest.amount = src.witoutvat 
WHERE col_tobefixed = 1 
  AND dest.`receipt_id` = src.receipt_id ;

Hopefully this helps when you have to match and update between two tables.

#5 building

I found this problem when I was looking for my own very complex join solution. This is an alternative solution to a more complex version of the problem, which I think may be useful.

I need to fill in the product? ID field in the activity table, where activities are numbered in units and units are numbered in levels (identified by the string?? N), so that activities can be identified by SKU (i.e. L1U1A1). The SKUs are then stored in another table.

I identified the following to get a list of activity ID and product ID:-

SELECT a.activity_id, w.product_id 
  FROM activities a 
  JOIN units USING(unit_id) 
  JOIN product_types USING(product_type_id) 
  JOIN web_products w 
    ON sku=CONCAT('L',SUBSTR(product_type_code,3), 'U',unit_index, 'A',activity_index)

I found this too complex to merge into SELECT in mysql, so I created a temporary table and added it with the update statement:

CREATE TEMPORARY TABLE activity_product_ids AS (<the above select statement>);

UPDATE activities a
  JOIN activity_product_ids b
    ON a.activity_id=b.activity_id 
  SET a.product_id=b.product_id;

I hope some people find this useful