MySQL query to update data from one table to another table

update query is needed for MySQL
--
-- Creation script with sample data 
--
 
CREATE TABLE emp (
   id int(9) unsigned NOT NULL auto_increment,
   empno varchar(6) NOT NULL,
   ename varchar(30) NOT NULL,
   address varchar(30),
   mystatus varchar(2), 
   PRIMARY KEY  (id)
) DEFAULT CHARSET=utf8;
 
 
INSERT INTO emp (id , empno, ename, address ,mystatus)
VALUES (1,'123456', 'PKM', 'Address1', 'OK');
 
INSERT INTO emp (id , empno, ename, address ,mystatus)
VALUES (2,'234567', 'KSB', 'Address2', 'OK');
 
INSERT INTO emp (id , empno, ename, address ,mystatus)
VALUES (3,'345678', 'KMM', 'Address3', 'OK');
 
 
CREATE TABLE emp_temp (
   id int(9) unsigned NOT NULL auto_increment,
   empno varchar(6) NOT NULL,
   ename varchar(30) NOT NULL,
   address varchar(30),
   PRIMARY KEY  (id)
) DEFAULT CHARSET=utf8;
 
 
INSERT INTO emp_temp (id , empno, ename, address )
VALUES (1,'123456', 'PKM', 'Add1');
 
INSERT INTO emp_temp (id , empno, ename, address )
VALUES (2,'234567', 'KSB', 'Add2');
 
INSERT INTO emp_temp (id , empno, ename, address )
VALUES (3,'345678', 'KMM', 'Add3');
 
INSERT INTO emp_temp (id , empno, ename, address )
VALUES (4,'456789', 'MMG', 'Add4');
 
 
Here I have two tables emp and emp_temp. 
Now I want to update the table emp from the table data emp_temp. That is the final result in the table emp is
 
1,'123456', 'PKM', 'Add1', 'OK'
2,'234567', 'KSB', 'Add2', 'OK'
3,'345678', 'KMM', 'Add3', 'OK'
4,'456789', 'MMG', 'Add4', ''
 
My friend told me to use ON DUPLICATE KEY UPDATE statement in this case.
Please help. I am novice to MYSQL.

Open in new window

LVL 7
karunamoorthyAsked:
Who is Participating?
 
SharathConnect With a Mentor Data EngineerCommented:
joep1978 - your query needs to be modifed little bit for the asker's requirement. check the attached.

mysql> select * from emp;
+----+--------+-------+----------+----------+
| id | empno  | ename | address  | mystatus |
+----+--------+-------+----------+----------+
|  1 | 123456 | PKM   | Address1 | OK       |
|  2 | 234567 | KSB   | Address2 | OK       |
|  3 | 345678 | KMM   | Address3 | OK       |
+----+--------+-------+----------+----------+
3 rows in set (0.00 sec)
 
mysql> select * from emp_temp;
+----+--------+-------+---------+
| id | empno  | ename | address |
+----+--------+-------+---------+
|  1 | 123456 | PKM   | Add1    |
|  2 | 234567 | KSB   | Add2    |
|  3 | 345678 | KMM   | Add3    |
|  4 | 456789 | MMG   | Add4    |
+----+--------+-------+---------+
4 rows in set (0.00 sec)
 
-- joep1978 solution won't update/insert your emp table.
mysql> replace into emp (id, empno,ename,address,mystatus) (select emp_temp.id,e
mp_temp.empno,emp_temp.ename,emp.address,'OK' from emp left join emp_temp on emp
.id=emp_temp.id);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0
 
mysql> select * from emp;
+----+--------+-------+----------+----------+
| id | empno  | ename | address  | mystatus |
+----+--------+-------+----------+----------+
|  1 | 123456 | PKM   | Address1 | OK       |
|  2 | 234567 | KSB   | Address2 | OK       |
|  3 | 345678 | KMM   | Address3 | OK       |
+----+--------+-------+----------+----------+
3 rows in set (0.00 sec)
 
-- infact try this.
mysql> replace into emp (id, empno,ename,address,mystatus)
    -> (select emp_temp.id,emp_temp.empno,emp_temp.ename,emp_temp.address,ifnull
(emp.mystatus,'')
    ->   from emp_temp left join emp on emp.id=emp_temp.id);
Query OK, 7 rows affected (0.01 sec)
Records: 4  Duplicates: 3  Warnings: 0
 
mysql> select * from emp;
+----+--------+-------+---------+----------+
| id | empno  | ename | address | mystatus |
+----+--------+-------+---------+----------+
|  1 | 123456 | PKM   | Add1    | OK       |
|  2 | 234567 | KSB   | Add2    | OK       |
|  3 | 345678 | KMM   | Add3    | OK       |
|  4 | 456789 | MMG   | Add4    |          |
+----+--------+-------+---------+----------+
4 rows in set (0.00 sec)

Open in new window

0
 
joep1978Commented:
replace into emp (id, empno,ename,address) (select emp_temp.id,emp_temp.empno,emp_temp.ename,emp.address from emp left join emp_temp on emp.id=emp_temp.id);
0
 
joep1978Commented:
Oops I meant

replace into emp (id, empno,ename,address,mystatus) (select emp_temp.id,emp_temp.empno,emp_temp.ename,emp.address,'OK' from emp left join emp_temp on emp.id=emp_temp.id);
0
 
SharathData EngineerCommented:

replace into emp (id, empno,ename,address,mystatus) 
(select emp_temp.id,emp_temp.empno,emp_temp.ename,emp_temp.address,ifnull(emp.mystatus,'')
  from emp_temp left join emp on emp.id=emp_temp.id);

Open in new window

0
 
karunamoorthyAuthor Commented:
Thank you Sharath_123.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.