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?
Add a comment
|
9 Answers
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'
5 Comments
vladkras
yep,
INNER JOIN is perfect in this situation. I also used CONCAT_WS to merge pruduct name and SKU from another table
Gellie Ann
Is there a way to do this using aliases?
Sequoya
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
sniffingdoggo
This doesn't work. tableB still has its own data w/o changing. wtools.io/paste-code/bzWA A sample based on OP and this answer.
Warren Sergent
@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.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
10 Comments
LeoSam
ive over 1k record with different names and value , here your saying just for 1st record
John Woo
you can just remove the
where clause or modify the where clause depending on your needs..LeoSam
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 ,!
John Woo
i don't know if i understood your question clearly, can you check my updated answer?
LeoSam
i did try both update, the 2nd one says effeced on 734 rows , i checked values still all 0 not changed
|
Second possibility is,
UPDATE TableB
SET TableB.value = (
SELECT TableA.value
FROM TableA
WHERE TableA.name = TableB.name
);
6 Comments
davidkonrad
Yes, no need for complicated joins, when all we need is to update a field with a value from another table.
frijj2k
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.
Muhwu
Also, in this scenario, table A and B can't be the same table due to SQL constraints.
Steverino
@frijj2k would this still be slow if
.name is indexed on both tables?
Zhaoping Lu
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.
|
UPDATE cities c, city_langs cl
SET c.fakename = cl.name
WHERE c.id = cl.city_id
2 Comments
Komal
This is simplest.
ArabianMaiden
Love this, very versatile
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**
;
Comments
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);
Comments
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
Comments
UPDATE tbl1
SET tbl1.columnName = (
SELECT tbl2.columnName
FROM tbl2
WHERE tbl2.id = tbl1.user_id
);
//WHERE tbl1.id = 7;
Comments
This code work fine for me:
mysql> update link_productgroepen set groep_id=(select groep_id from link_productgroepen_bck where link_productgroepen.sku=link_productgroepen_bck.sku);
link_productgroepen_bck contains a backup of the table link_productgroepen ,so the structure is the same. In order to update the link_productgroepen table I need to drop it and to create a new empty clone of it that gets filled with the new values provided by an other website by an api. This new dataset needs to be complemented by information present in 2 columns in the link_productgroepen_bck table. The code above copies the contents of the groep_id column in the link_productgroepen_bck table to the renewed ink_productgroepen table if the sku value in both tables is the same. The other backupped column is copied by the same mysql command but with the other column name.