mysql update column with value from another table

Question

I have two tables  both looking like  id  name  value                     1   Joe     22 2   Derk    30   I need to copy the value of value from tableA to tableB based on check name in each table   Any tips for this UPDATE statement

User · Answer

UPDATE    cities c            city langs cl     SET       c fakename   cl name    WHERE     c id   cl city id

User · Answer

In addition to this answer if you need to change tableB value according to tableA value dynamically you can do for example   UPDATE tableB INNER JOIN tableA ON tableB name   tableA name SET tableB value   IF tableA value  gt  0  tableA value  tableB value  WHERE tableA name    Joe

User · Answer

If you have common field in both table then it s so easy         Table-1   table where you want to update  Table-2   table where you from take data    make query in Table-1 and find common field value  make a loop and find all data from Table-2 according to table 1 value  again make update query in table 1       qry asseet list   mysql query  SELECT  primary key field  FROM  table-1       resultArray   array    while   row   mysql fetch array  qry asseet list      resultArray      row       foreach  resultArray as  rec          a    rec  primary key field          cuttable qry   mysql query  SELECT   FROM  Table-2  WHERE  key field name     a          cuttable   mysql fetch assoc  cuttable qry          echo  x   cuttable  Table-2 field    echo            echo  y   cuttable  Table-2 field   echo            echo  z   cuttable  Table-2 field   echo               k   mysql query  UPDATE  Table-1  SET  summary style      x    summary color      y    summary customer      z  WHERE  summary laysheet number     a          if   k            echo  done         else           echo mysql error

User · Answer

you need to join the two tables    for instance you want to copy the value of name from tableA into tableB where they have the same ID  UPDATE tableB t1          INNER JOIN tableA t2               ON t1 id   t2 id SET t1 name   t2 name  WHERE t2 name    Joe    UPDATE 1  UPDATE tableB t1          INNER JOIN tableA t2               ON t1 id   t2 id SET t1 name   t2 name    UPDATE 2  UPDATE tableB t1          INNER JOIN tableA t2               ON t1 name   t2 name SET t1 value   t2 value

User · Answer

The second option is feasible also if you re using safe updates mode  and you re getting an error indicating that you ve tried to update a table without a WHERE that uses a KEY column   by adding    UPDATE TableB   SET TableB value       SELECT TableA value       FROM TableA       WHERE TableA name   TableB name         where TableB id  lt  X

User · Answer

Store your data in temp table   Select   into tempTable from table1   Now update the column   UPDATE table1     SET table1 FileName    select FileName from tempTable where tempTable id   table1 ID

User · Answer

Second possibility is   UPDATE TableB  SET TableB value         SELECT TableA value      FROM TableA     WHERE TableA name   TableB name

User · Answer

In my case  the accepted solution was just too slow  For a table with 180K rows the rate of updates was about 10 rows per second  This is with the indexes on the join elements   I finally resolved my issue using a procedure   CREATE DEFINER  my procedure      PROCEDURE  rescue    BEGIN     declare str VARCHAR 255  default         DECLARE n INT DEFAULT 0      DECLARE i INT DEFAULT 0      DECLARE cur name VARCHAR 45  DEFAULT         DECLARE cur value VARCHAR 10000  DEFAULT         SELECT COUNT    FROM tableA INTO n      SET i 0      WHILE i lt n DO        SELECT namea valuea FROM tableA limit i 1 INTO cur name cur value        UPDATE tableB SET nameb cur name where valueb cur value        SET i   i   1      END WHILE   END   I hope it will help someone in the future like it helped me

[mysql] mysql update column with value from another table

Examples related to mysql

Examples related to sql

Examples related to sql-update