Iam bit new to Mysql, What is my question is, how to Update or Insert Table_1 column data to Table_2 column data with matching the relevant Id's from Table_1.
For Eg.
Table_1 Table_2
rand | cpn rand | cpn
1 4 2 0
2 7 3 0
3 2 5 0
4 1 1 0
5 7 4 0
The above mentioned table are the current structure of the my tables, my output should be like this,
Table_2
rand | cpn
2 7
3 2
5 7
1 4
4 1
I want to match the ids from table_1 and update in table_2, accordingly Thanks in Advance :)
In mysql you can use joins in updates to achive the expected results:
update table2 t2 inner join table1 t1 on t2.rand=t1.rand
set t2.cpn=t1.cpn
Well, you should JOIN the tables so you can use the values, like so:
UPDATE Table_2 t2
LEFT JOIN Table_1 t1 on t2.rand = t1.rand
SET t2.cpn = t1.cpn;
You can use this. it INSERT OR UPDATE Records. You only must have a unique INDEX or PRIMARY KEY on rand.
INSERT INTO Table_2
(SELECT * FROM Table_1 )
ON DUPLICATE KEY UPDATE cpn = VALUES(cpn);
SAMPLE
MariaDB []>
MariaDB []> CREATE TABLE `Table_1` (
-> `rand` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
-> `cpn` INT(11) DEFAULT NULL,
-> PRIMARY KEY (`rand`)
-> ) ENGINE=INNODB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.16 sec)
MariaDB []>
MariaDB []> INSERT INTO `Table_1` (`rand`, `cpn`)
-> VALUES
-> (1, 4),
-> (2, 7),
-> (3, 2),
-> (4, 1),
-> (5, 7);
Query OK, 5 rows affected (0.02 sec)
Records: 5 Duplicates: 0 Warnings: 0
MariaDB []>
MariaDB []>
MariaDB []> CREATE TABLE `Table_2` (
-> `rand` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
-> `cpn` INT(11) DEFAULT NULL,
-> PRIMARY KEY (`rand`)
-> ) ENGINE=INNODB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.30 sec)
MariaDB []>
MariaDB []> INSERT INTO `Table_2` (`rand`, `cpn`)
-> VALUES
-> (1, 0),
-> (2, 0),
-> (3, 0),
-> (4, 0),
-> (5, 0);
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0
MariaDB []>
MariaDB []>
MariaDB []> SELECT * FROM Table_1;
+------+------+
| rand | cpn |
+------+------+
| 1 | 4 |
| 2 | 7 |
| 3 | 2 |
| 4 | 1 |
| 5 | 7 |
+------+------+
5 rows in set (0.00 sec)
MariaDB []>
MariaDB []> SELECT * FROM Table_2;
+------+------+
| rand | cpn |
+------+------+
| 1 | 0 |
| 2 | 0 |
| 3 | 0 |
| 4 | 0 |
| 5 | 0 |
+------+------+
5 rows in set (0.00 sec)
MariaDB []>
MariaDB []> INSERT INTO Table_2
-> (SELECT * FROM Table_1 )
-> ON DUPLICATE KEY UPDATE cpn = VALUES(cpn);
Query OK, 10 rows affected (0.00 sec)
Records: 5 Duplicates: 5 Warnings: 0
MariaDB []>
MariaDB []> SELECT * FROM Table_2;
+------+------+
| rand | cpn |
+------+------+
| 1 | 4 |
| 2 | 7 |
| 3 | 2 |
| 4 | 1 |
| 5 | 7 |
+------+------+
5 rows in set (0.00 sec)
MariaDB []>
This should work:
update table2 t2 set cpn = (select cpn from table1 where rand = t2.rand)