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?
This question is related to
mysql
sql
sql-update
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**
;
UPDATE cities c,
city_langs cl
SET c.fakename = cl.name
WHERE c.id = cl.city_id
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);
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.
$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();
}
}
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
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
Second possibility is,
UPDATE TableB
SET TableB.value = (
SELECT TableA.value
FROM TableA
WHERE TableA.name = TableB.name
);
Source: Stackoverflow.com