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

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

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

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

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

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

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

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

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

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

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

[mysql] MySQL - UPDATE query based on SELECT Query

Examples related to mysql

Examples related to select

Examples related to sql-update