Updating MySQL crosstab using WHERE

Question

Here is a simple query to update a product table with data from helper .

 UPDATE product p INNER JOIN helper h ON p.productcode = h.productcode SET p.picture = h.picture; 

But if I add WHERE p.gotpicture=0 at the end to only update records with p.gotpicture = 0, and not the whole table, then the query updates 0 rows. Why?

Answer 1

I'v tester and it works great for me.

But you can check them out:

 UPDATE product p , helper h SET p.picture = h.picture WHERE p.gotpicture=0 AND p.productcode = h.productcode; 

or

 UPDATE product p SET p.picture = (select h.picture from helper h where p.productcode = h.productcode) WHERE p.gotpicture=0 AND p.productcode in (select h.productcode from helper h); 

Answer 2

Your problem is very easily solved, just follow this.

  UPDATE product p,helper h SET p.picture = h.picture where p.productcode = h.productcode and p.gotpicture=0; 

Just try this code above, you will probably be able to overcome your problem.