Update one MySQL table with values from another

by 
UPDATE tobeupdated
INNER JOIN original ON (tobeupdated.value = original.value)
SET tobeupdated.id = original.id

That should do it, and really its doing exactly what yours is. However, I prefer ‘JOIN’ syntax for joins rather than multiple ‘WHERE’ conditions, I think its easier to read

As for running slow, how large are the tables? You should have indexes on tobeupdated.value and original.value

EDIT:
we can also simplify the query

UPDATE tobeupdated
INNER JOIN original USING (value)
SET tobeupdated.id = original.id

USING is shorthand when both tables of a join have an identical named key such as id. ie an equi-join – http://en.wikipedia.org/wiki/Join_(SQL)#Equi-join

More Related Contents:

  1. Which DB technology is sued to solve the following search issue? [closed]
  2. i have an error which i dont understand. can you help me? [closed]
  3. MySQL pivot row into dynamic number of columns
  4. Rank function in MySQL
  5. MySQL select 10 random rows from 600K rows fast
  6. Count the number of occurrences of a string in a VARCHAR field?
  7. Find nearest latitude/longitude with an SQL query
  8. MySQL: Split comma separated list into multiple rows
  9. Most efficient way to get table row count
  10. How do I check if a column is empty or null in MySQL?
  11. Subqueries with EXISTS vs IN – MySQL
  12. Entity-Attribute-Value Table Design
  13. How do I decide when to use right joins/left joins or inner joins Or how to determine which table is on which side?
  14. MySQL Workbench: How to keep the connection alive
  15. Filter by COUNT(*)?
  16. MySQL order by “best match”
  17. How to select rows that have current day’s timestamp?
  18. MySQL concatenation operator
  19. SQL Group By with an Order By
  20. How create json format with group-concat mysql?
  21. MySql – Way to update portion of a string?
  22. Using reserved words in column names
  23. MySQL INNER JOIN select only one row from second table
  24. Storing IPv6 Addresses in MySQL
  25. Insert/ Update random date in MySQL
  26. What is the difference between SQL and MySQL? [closed]
  27. how to use a like with a join in sql?
  28. Get total hours worked in a day mysql
  29. MySQL: Computed Column
  30. mySQL query to search all tables within a database for a string?

Leave a Comment