MySQL – UPDATE query based on SELECT Query

MySQLselectsql-update

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?

Best Answer

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.

MS SQL

UPDATE
    Sales_Import
SET
    Sales_Import.AccountNumber = RAN.AccountNumber
FROM
    Sales_Import SI
INNER JOIN
    RetrieveAccountNumber RAN
ON 
    SI.LeadID = RAN.LeadID;

MySQL and MariaDB

UPDATE
    Sales_Import SI,
    RetrieveAccountNumber RAN
SET
    SI.AccountNumber = RAN.AccountNumber
WHERE
    SI.LeadID = RAN.LeadID;

Mysql – How to output MySQL query results in CSV format

From Save MySQL query results into a text or CSV file:

SELECT order_id,product_name,qty
FROM orders
WHERE foo = 'bar'
INTO OUTFILE '/var/lib/mysql-files/orders.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

Note: That syntax may need to be reordered to

SELECT order_id,product_name,qty
INTO OUTFILE '/var/lib/mysql-files/orders.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM orders
WHERE foo = 'bar';

in more recent versions of MySQL.

Using this command, columns names will not be exported.

Also note that /var/lib/mysql-files/orders.csv will be on the server that is running MySQL. The user that the MySQL process is running under must have permissions to write to the directory chosen, or the command will fail.

If you want to write output to your local machine from a remote server (especially a hosted or virtualize machine such as Heroku or Amazon RDS), this solution is not suitable.

Best Answer

Related Solutions

SQL update from one Table to another based on a ID match

MS SQL

MySQL and MariaDB

Mysql – How to output MySQL query results in CSV format

Related Topic