mysql update column with value from another table

user_898454 0
Viewed 479009

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?

sql-update mysql sql
8 Answers
    UPDATE    cities c,
          city_langs cl
    SET       c.fakename = cl.name
   WHERE     c.id = cl.city_id
user_32904660

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_49213850

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

user_43604330
Related