Help constructing query - Compare columns and replace numbers

Question

I have a feeling that this query is pretty easy to construct, I just can't figure it out.

I want to replace all numbers in table X column C, with numbers in table Z column A, where numbers from table X column C matches numbers in table Z column B.

I hope that makes sense. Perhaps a little background information will make it clearer. I've converted from one CMS to another, and the module I used to convert mapped the ids to the new database. Table X column A is the new id's. Table X column B is the old id's. Table Z is the table for an image gallery that I migrated, and column C contains the id's of the images owners.

Can anyone crack this nut?

Answer 1

UPDATE X 
SET    C=Z.A 
FROM   Z 
WHERE  X.C = Z.B

Answer 2

Try this:

UPDATE X INNER JOIN Z ON X.C = Z.B SET X.C = Z.A

Let's start with

X.foo X.C
  A    1
  B    2

and

Z.A   Z.B
 2     1
 3     2

Joining the tables results in

X.foo X.C Z.B Z.A
  A    1   1   2
  B    2   2   3

The UPDATE takes every row and replaces the value in X.C with the value in Z.A from the same row, resulting in:

X.foo X.C
  A    2
  B    3

You may get unwanted results if Z.B is not UNIQUE.

EDIT: This is only an example. Choose the JOIN carefully - for example, you may or may not prefer a LEFT JOIN. Think about the case where X.C takes a value that does not exist in Z.B and decide if you want X.C unchanged or set to NULL.

Answer 3

Just for the completeness the first answer syntax error is due to the FROM. Update syntax need no FROM, as the UPDATE defines all the tables and joins and the SET actually defines what is to be updated. It's a bit backwards as far as straight semantics goes and not as nice as SELECT ... FROM ... which sounds like natural language. For example, you would say

UPDATE X, Z
SET    X.C = Z.A 
WHERE  X.C = Z.B

even though you will not be updating the table Z at all, but one gets used to it. Good part is that you would write the UPDATE part exactly like you would write the FROM part in the SELECT queries - so it can be as complex as you need it to be and still makes it easy to turn SELECT queries into UPDATE queries (previewing your UPDATE queries sets with SELECT is a good practice on any ad-hoc queries on live data). To go from UPDATE query to SELECT just replace UPDATE with FROM, SET with SELECT, equal signs with commas (in the SELECT part) and of course put the select on top

SELECT X.C, Z.A
FROM   X, Z
WHERE  X.C = Z.B

Will give you a preview of the operation that'll get executed, listing the old value of the field and new value that'll get assigned to it.

Mysql has decent docs. You might prefer to read them backwards - first the examples toward the end, then the full reference definitions. Also usually you'll find some really usefull comments below the articles.

Answer 4

I solved it with titanoboas first comment, running this query:

UPDATE X LEFT JOIN Z ON Z.A = X.C SET X.C = Z.A WHERE X.C = Z.B

I see now that you adviced me to not use LEFT JOIN, but fortunately it worked as expected. I then tried gorans example, and it worked perfect as well. Gorans example is easier to understand, allthough this was a good lesson for me to learn joins.

Thanks for the help!