0

I have a table users with existing data, and I upload updated data to a temporary table users_temp.

I am trying to create two queries, one to update name and department_id rows in users with data from users_temp on matching id.

And another to remove rows in users which don't have matching id in users_temp.

Please help.

Dean
  • 7,814
  • 8
  • 30
  • 31

2 Answers2

2

You can do this by adding a join in your update.

UPDATE users u 
INNER JOIN users_temp ut on u.id = ut.id //or whatever the matching fields are
SET u.name = ut.name, u.department_id = ut.department_id;

I'm sure someone will have a more efficient example for the second query but this would do the trick:

Delete all rows which has no id existing in another table

Community
  • 1
  • 1
Shad Mickelberry
  • 291
  • 1
  • 4
1
update users
inner join users_temp using (id)
set users.name = users_temp.name,
users.department_id = users_temp.department_id

delete from users
where not exists (select * from users_temp where users_temp.id = users.id)
pigmej
  • 169
  • 5
  • Why use `LEFT JOIN` if you're just going to filter out the non-matching rows in the `WHERE` clause? Use an `INNER JOIN` so they're not included in the firstplace. – Barmar Oct 26 '15 at 23:55
  • Thank you, it worked. Could you please add a query that copies a row from users_temp into users if it doesn't exist in users? – Dean Oct 27 '15 at 00:57