Updating SQL table with thousands of values

Question

I have a single MSSQL table that I need to update a column with different values.

To update a single record, I would use a query like this:

UPDATE products 
SET price = 2.08 
WHERE product_id = 'B22991-12'

I need to do this about 7,000 times, so how would I do this? The new prices are currently in a spreadsheet.

when you have no alghorothmen with criteria make a new table fill it up and join it — nbk, May 12 '23 at 22:57
Need some clarification. Where are the price and product id coming from? — Kevin, May 12 '23 at 22:58
When you execute this statement 7000 times, you will do it (about) 6999 times without any results. You should share more details about the other (about) 6999 cases. — Luuk, May 13 '23 at 07:00
Use: [How can I import an Excel file into SQL Server?](https://stackoverflow.com/questions/39610133/how-can-i-import-an-excel-file-into-sql-server), and then the given answer. Please, use [edit] if that does not solve your question, and improve the question to 1 specific problem. (Like: `problem is importing`, or `Problem is updating`) — Luuk, May 14 '23 at 15:48

Chor · Accepted Answer · 2023-05-13T01:05:35.217

1

You can achieve this by having a table that holds the correct prices of your products. Let’s say product_new_prices.

UPDATE a
SET a.price = b.price
FROM products a
INNER JOIN product_new_prices b on a.product_id = b.product_id

edited May 13 '23 at 01:05

answered May 13 '23 at 00:48

Chor

36
2

score 0 · Answer 2 · answered May 12 '23 at 23:46

You don't specify your DBMS. But many of them support the UPDATE ... FROM statement.

If your product table is like so:

CREATE TABLE products(prd_id,prdname,price)  AS
          SELECT 'B22991-12','bread'     ,1.08
UNION ALL SELECT 'B22991-13','butter'    ,1.09
UNION ALL SELECT 'B22991-14','marmalade' ,1.09
;

Create an update source table like so:

CREATE TABLE upds(prd_id,price)  AS
          SELECT 'B22991-12',2.08
UNION ALL SELECT 'B22991-13',3.09
UNION ALL SELECT 'B22991-14',4.09
;

And then:

UPDATE products t
  SET  price=s.price
FROM   upds s
WHERE  s.prd_id=t.prd_id;

Updating SQL table with thousands of values

2 Answers2