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?

Answer 1

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.

Answer 2

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

Hope this works for you.

Answer 3

Easy in MySQL:

UPDATE users AS U1, users AS U2 
SET U1.name_one = U2.name_colX
WHERE U2.user_id = U1.user_id

Answer 4

If somebody is seeking to update data from one database to another no matter which table they are targeting, there must be some criteria to do it.

This one is better and clean 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 

Traaa! It works great!

With the above understanding, you can modify the set fields and "on" criteria to do your work. You can also perform the checks, then pull the data into the temp table(s) and then run the update using the above syntax replacing your table and column names.

Hope it works, if not let me know. I will write an exact query for you.

Answer 5

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 ;

Hope this will help you in a case where you have to match and update between two tables.

Answer 6

I found this question in looking for my own solution to a very complex join. This is an alternative solution, to a more complex version of the problem, which I thought might be useful.

I needed to populate the product_id field in the activities table, where activities are numbered in a unit, and units are numbered in a level (identified using a string ??N), such that one can identify activities using an SKU ie L1U1A1. Those SKUs are then stored in a different table.

I identified the following to get a list of activity_id vs 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 that that was too complex to incorporate into a SELECT within mysql, so I created a temporary table, and joined that 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 someone finds this useful

Answer 7

UPDATE [table_name] AS T1,
      (SELECT [column_name] 
        FROM [table_name] 
        WHERE [column_name] = [value]) AS T2 
  SET T1.[column_name]=T2.[column_name] + 1
WHERE T1.[column_name] = [value];

Answer 8

You can update values from another table using inner join like this

UPDATE [table1_name] AS t1 INNER JOIN [table2_name] AS t2 ON t1.column1_name] = t2.[column1_name] SET t1.[column2_name] = t2.column2_name];

Follow here to know how to use this query http://www.voidtricks.com/mysql-inner-join-update/

or you can use select as subquery to do this

UPDATE [table_name] SET [column_name] = (SELECT [column_name] FROM [table_name] WHERE [column_name] = [value]) WHERE [column_name] = [value];

query explained in details here http://www.voidtricks.com/mysql-update-from-select/

Answer 9

You can use:

UPDATE Station AS st1, StationOld AS st2
   SET st1.already_used = 1
 WHERE st1.code = st2.code

Answer 10

For same table,

UPDATE PHA_BILL_SEGMENT AS PHA,
     (SELECT BILL_ID, COUNT(REGISTRATION_NUMBER) AS REG 
       FROM PHA_BILL_SEGMENT
        GROUP BY REGISTRATION_NUMBER, BILL_DATE, BILL_AMOUNT
        HAVING REG > 1) T
    SET PHA.BILL_DATE = PHA.BILL_DATE + 2
 WHERE PHA.BILL_ID = T.BILL_ID;

Answer 11

I had an issue with duplicate entries in one table itself. Below is the approaches were working for me. It has also been advocated by @sibaz.

Finally I solved it using the below queries:

1. The select query is saved in a temp table

   IF OBJECT_ID(N'tempdb..#New_format_donor_temp', N'U') IS NOT NULL
       DROP TABLE #New_format_donor_temp;
   
   select *
   into #New_format_donor_temp
   from DONOR_EMPLOYMENTS
   where DONOR_ID IN (
     1, 2
   )
   
   -- Test New_format_donor_temp
   -- SELECT *
   -- FROM #New_format_donor_temp;
   

2. The temp table is joined in the update query.

   UPDATE de
   SET STATUS_CD=de_new.STATUS_CD, STATUS_REASON_CD=de_new.STATUS_REASON_CD, TYPE_CD=de_new.TYPE_CD
   FROM DONOR_EMPLOYMENTS AS de
     INNER JOIN #New_format_donor_temp AS de_new ON de_new.EMP_NO = de.EMP_NO
   WHERE
     de.DONOR_ID IN (
       3, 4
   )
   

I not very experienced with SQL please advise any better approach you know.

Above queries are for MySql server.

Answer 12

if you are updating from a complex query. The best thing is create temporary table from the query, then use the temporary table to update as one query.

    DROP TABLE IF EXISTS cash_sales_sums;
CREATE TEMPORARY TABLE cash_sales_sums as
SELECT tbl_cash_sales_documents.batch_key, COUNT(DISTINCT tbl_cash_sales_documents.cash_sale_number) no_of_docs,
  SUM(tbl_cash_sales_documents.paid_amount) paid_amount, SUM(A.amount - tbl_cash_sales_documents.bonus_amount - tbl_cash_sales_documents.discount_given) amount,
  SUM(A.recs) no_of_entries FROM 
        tbl_cash_sales_documents
    RIGHT JOIN(
        SELECT
            SUM(
                tbl_cash_sales_transactions.amount
            )amount,
            tbl_cash_sales_transactions.cash_sale_document_id,
            COUNT(transaction_id)recs
        FROM
            tbl_cash_sales_transactions
        GROUP BY
            tbl_cash_sales_transactions.cash_sale_document_id
    )A ON A.cash_sale_document_id = tbl_cash_sales_documents.cash_sale_id
    GROUP BY
        tbl_cash_sales_documents.batch_key
ORDER BY batch_key;





UPDATE tbl_cash_sales_batches SET control_totals = (SELECT amount FROM cash_sales_sums WHERE cash_sales_sums.batch_key = tbl_cash_sales_batches.batch_key LIMIT 1),
expected_number_of_documents = (SELECT no_of_docs FROM cash_sales_sums WHERE cash_sales_sums.batch_key = tbl_cash_sales_batches.batch_key),
computer_number_of_documents = expected_number_of_documents,  computer_total_amount = control_totals
WHERE batch_key IN (SELECT batch_key FROM cash_sales_sums);