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  gt  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

User · Answer

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:

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;

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.

User · Answer

You can use   UPDATE Station AS st1  StationOld AS st2    SET st1 already used   1  WHERE st1 code   st2 code

User · 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  gt  end dts   VALID       -- where clause can go here   ANSI SQL syntax   UPDATE tableA SET validation check         SELECT if start DTS  gt  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

User · Answer

Easy in MySQL   UPDATE users AS U1  users AS U2  SET U1 name one   U2 name colX WHERE U2 user id   U1 user id

User · Answer

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  gt  1  T     SET PHA BILL DATE   PHA BILL DATE   2  WHERE PHA BILL ID   T BILL ID

User · Answer

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

User · Answer

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   lt the above select statement gt     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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[mysql] MySQL - UPDATE query based on SELECT Query

Examples related to mysql

Examples related to select

Examples related to sql-update