Fairly new to SQL and having an issue updating some values in a table using another table. Version is SQL Server 2005.
Basically we have our main sales data table [Sales Data]. Within this there are a lot of columns but the one's we need to focus on are
[Product] - The product code for the row in question. Same value can appear many different times.
[Product Group 1] - A numeric value
[Product Group 2] - A numeric value
We've identified a number of produts that need to have their product groups changed retrospectively (the table contains 2 years data). I have uploaded a table to the database called produpdate. This contains the same 3 columns as above but this time each product code is listed once with it's 2 new product group values next to it.
I am trying to write a query that will update the product group 1 and 2 in the sales data table for every line where the product matches an entry in produpdate with the product group entries from that table.
I've tried a number of different queries, each with its own problem. I thought I had it using a sub-query but then got an issue with the sub query returning multiple rows after an '='.
Could anyone shed some light on the type of query I may need?
Update [Sales Data]
Set [Product Group 1] = produpdate.[Product Group 1],
[Product Group 2] = produpdate.[Product Group 2]
From [Sales Data}
Inner Join produpdate On produpdate.Product = [Sales Data].Product