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?

score 588 · Accepted Answer · edited Dec 18 '18 at 01:10

588

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 > 0, tableA.value, tableB.value)
WHERE tableA.name = 'Joe'

edited Dec 18 '18 at 01:10

Community

1
1

answered Oct 13 '13 at 14:35

RafaSashi

16,483
8
84
94

yep, `INNER JOIN` is perfect in this situation. I also used `CONCAT_WS` to merge pruduct name and SKU from another table – vladkras Jul 16 '14 at 05:43
2

Is there a way to do this using aliases? – Gellie Ann Dec 28 '16 at 05:10
I tryed this but no success, because the "affected rows" count gives me 5690, but the total rows are 59643, why? this is the query: `UPDATE participants_registrations INNER JOIN participants ON participants.id = participants_registrations.participantId INNER JOIN registrations ON registrations.id = participants_registrations.registrationId LEFT JOIN groups ON (groups.id = registrations.groupId) SET registrations.groupId = groups.id, registrations.groupName = groups.name, participants.memberOfGroupName = groups.name` – Sequoya Jun 28 '18 at 04:33
This doesn't work. tableB still has its own data w/o changing. https://wtools.io/paste-code/bzWA A sample based on OP and this answer. – sniffingdoggo Feb 19 '20 at 11:14
1

@sniffingdoggo That's because your datasets in Table A and B don't match at all. Your `INNER JOIN` is joining on `name` and your WHERE is looking for TableA.name. The JOIN won't happen, since there's no records to join on, and therefore you can't update TableB where the name is Joe, because there aren't any records matching that criteria. – Warren Sergent Oct 27 '22 at 04:14

John Woo · Answer 2 · 2012-07-29T12:07:28.143

211

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

edited Jul 29 '12 at 12:07

answered Jul 29 '12 at 12:02

John Woo

258,903
69
498
492

1

ive over 1k record with different names and value , here your saying just for 1st record – LeoSam Jul 29 '12 at 12:06
you can just remove the `where` clause or modify the `where` clause depending on your needs.. – John Woo Jul 29 '12 at 12:07
also table B got more records than table A , my idea is check from table B to a if the name exist copy the value of " value" to table B ,! – LeoSam Jul 29 '12 at 12:07
i don't know if i understood your question clearly, can you check my updated answer? – John Woo Jul 29 '12 at 12:11
i did try both update, the 2nd one says effeced on 734 rows , i checked values still all 0 not changed – LeoSam Jul 29 '12 at 12:19
can you give atleast records from tableA and tableB and with the new values of tableB? willing to help here :) – John Woo Jul 29 '12 at 12:20
I got it error was here SET t1.value = t2.value , its should beSET t2.value = t1.value other wise will update the main table which happen to me :) glad ive backup thanks for the tips works like sharp – LeoSam Jul 29 '12 at 12:37
thanks for this @JW웃 I have been searching around for this functionality for a while, just wondering, what would a select statement using the inner join look like (i'm curious to see what is happening here) Tnx. – shnozolla May 02 '13 at 15:12
@shnozolla something like this, `SELECT t1.*, t2.* FROM tableB t1 INNER JOIN tableA t2 ON t1.name = t2.name` – John Woo May 02 '13 at 15:16
Does it have to be an inner join? – felwithe May 14 '19 at 12:31

score 120 · Answer 3 · edited May 15 '14 at 12:36

120

Second possibility is,

UPDATE TableB 
SET TableB.value = (
    SELECT TableA.value 
    FROM TableA
    WHERE TableA.name = TableB.name
);

edited May 15 '14 at 12:36

Michel Ayres

5,891
10
63
97

answered Apr 11 '14 at 22:08

Samir Alajmovic

3,247
3
26
28

9

Yes, no need for complicated joins, when all we need is to update a field with a value from another table. – davidkonrad Feb 20 '15 at 12:39
17

Yes this works fine but is very, very slow on large datasets. If you're working with small tables this method is fine but I recommend the JOIN as shown above for anything else. – frijj2k Dec 04 '15 at 10:11
1

Also, in this scenario, table A and B can't be the same table due to SQL constraints. – Muhwu Oct 27 '16 at 12:00
@frijj2k would this still be slow if `.name` is indexed on both tables? – Steverino Jan 06 '17 at 17:17
@frijj2k - When I did this using the JOIN method, then reset the tables and repeated with the above method, the JOIN method was slower. – Michael Sims Oct 19 '21 at 18:33
This answer is wrong. 2 issues: 1) This sql will update all rows in TableB. 2) If didn't find matched name in TableA, the subquery will return NULL thus TableB.value will be set to NULL. – Zhaoping Lu Mar 03 '23 at 06:55

score 29 · Answer 4 · answered Oct 04 '19 at 15:40

29

    UPDATE    cities c,
          city_langs cl
    SET       c.fakename = cl.name
   WHERE     c.id = cl.city_id

answered Oct 04 '19 at 15:40

aitbella

958
9
19

3

This is simplest. – Komal Nov 18 '21 at 13:17
1

Love this, very versatile – ArabianMaiden May 04 '22 at 18:31

score 4 · Answer 5 · edited Mar 07 '17 at 07:39

4

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 < X**  
;

edited Mar 07 '17 at 07:39

SymbolixAU

25,502
4
67
139

answered Feb 07 '17 at 10:06

raul7

171
1
10

score 3 · Answer 6 · edited Mar 09 '19 at 11:26

3

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

edited Mar 09 '19 at 11:26

Cody Gray - on strike

239,200
50
490
574

answered May 15 '18 at 14:12

Abid Mahmood

27
3

score 1 · Answer 7 · answered May 02 '19 at 22:47

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

mysql update column with value from another table

7 Answers7

Linked

Related