Update query uses same value for each row

Question

I have a SQLite database, which needs to be updated. Using the update statement this should be easy.

But things aren't going as planned:

This is the complete query:

UPDATE products_new SET name =   
(
  SELECT products_sap.title 
  FROM products_sap, products_new 
  WHERE products_sap.id_a = products_new.product_id
);

As part of my debugging scheme I'm trying out each (sub)query.

This query ( identical to the one used in the above update ) shows me the correct records:

SELECT products_sap.id, products_sap.title 
FROM products_sap, products_new 
WHERE products_sap.id_a = products_new.product_id;

Example:

id | title
----------------------
 1 | Lorum Ipsum
 2 | Lorum Not Ipsum
 3 | Ipsum Lorum

But: all my rows in the updated table have identical name values * gasps *.

SELECT products_new.name from products_new;

Example:

id | name
----------------------
 1 | Lorum Ipsum
 2 | Lorum Ipsum
 3 | Lorum Ipsum

So my question is: how can I updated each row with it's relevant name value?

Similar to: http://stackoverflow.com/questions/3845718/sql-how-to-update-table-values-from-another-table-with-the-same-user-name — xQbert, Nov 28 '11 at 22:38
Thanks @xQbert. I did see that answer, but did not see the problem in my code. Staring at the problem too long got to me I guess. — Webdevotion, Nov 28 '11 at 22:51

João Silva · Accepted Answer · 2011-11-28T22:43:35.830

0

That happens because the products_new table in the inner query is hiding the products_new table in the UPDATE clause. Thus, you never actually join the rows from the table you are updating.

You need to make sure that for each row you are trying to update, its id (product_id) is joined with the table (products_sap) that contains the new value:

UPDATE products_new x 
SET name = (SELECT products_sap.title 
            FROM products_sap 
            WHERE products_sap.id_a = x.product_id);

edited Nov 28 '11 at 22:43

answered Nov 28 '11 at 22:38

João Silva

89,303
29
152
158

Thanks - it's working now. I removed the `products_new` from the inner query: `from products_sap, products_new` is now `from products_sap` in my inner query. You do have a little typo in your answer: remove the `x` right after the `update products_new`. – Webdevotion Nov 28 '11 at 22:46
It is actually an alias for the table, so that you can reference it in the inner query with just `x` instead of `products_new`. – João Silva Nov 28 '11 at 23:03
Right. I think it's time for bed : ) Thanks @joão. – Webdevotion Nov 28 '11 at 23:19

score 0 · Answer 2 · answered Nov 28 '11 at 22:42

I'm not completely familiar with SQLite, but you can do something like the following in other dialects:

UPDATE products_new SET name = products_sap.title
   FROM products_new 
   INNER JOIN  products_sap 
   ON products_new.product_id = products_sap.id_a

Update query uses same value for each row

2 Answers2