CODEWITHSUNDEEP
sql-server-2008t-sqlsql-update
Tom
Tom

Reputation: 4577

Update table with data from same table

Trying to write a query to update a table with data from the same table.

Basically we have two types of data in this table, budget and forecast, split up by customer, product, fiscal year and month.

A sample of the data would be as follows:

CustomerCosts
CustomerNo     Product     FiscalYear    Month    DataType    DataField    Q    R    G
CU01           REG         2014          1        Budget      Commission   10   2    20
CU01           REG         2014          1        Budget      Warehouse    5    5    25
CU01           REG         2014          2        Budget      Commission   30   1    30
CU01           REG         2014          2        Budget      Warehouse    12   2    24
CU01           REG         2014          1        Forecast    Commission   10   2    20
CU01           REG         2014          1        Forecast    Warehouse    5    5    25
CU01           REG         2014          2        Forecast    Commission   30   1    30
CU01           REG         2014          2        Forecast    Warehouse    12   2    24

Basically users are going to want to copy from one DataType to another. For example, copy 2014 Budget into 2014 Forecast.

I came up with this query but while it seems to work the row updated count shows more rows are being updated than should be.

UPDATE CustomerCosts 
SET CustomerCosts.Q = f.Q, 
CustomerCosts.R = f.R,
CustomerCosts.G = f.G
FROM CustomerCosts
JOIN CustomerCosts f
ON CustomerCosts.CustomerNo = f.CustomerNo AND 
CustomerCosts.Product = f.Product AND 
CustomerCosts.Month = f.Month AND 
CustomerCosts.DataField = f.DataField
WHERE CustomerCosts.FiscalYear = 2014 AND 
CustomerCosts.DataType = N'Forecast' AND 
f.FiscalYear = 2014 AND
f.DataType = N'Budget'

Do I have this query right or am I missing something?

Thanks.

Upvotes: 1

Views: 293

Answers (1)

Bogdan Bogdanov
Bogdan Bogdanov

Reputation: 1723

I it looks OK to me. But to be sure it is always good idea to make a test. Copy table CustomerCosts into a new temp table using 

SELECT * INTO Test_CustomerCosts FROM CustomerCosts

After that try update (change script so it works on Test table) and see results.

Try to modify your query to select:

select 
CustomerCosts.*, f.*
FROM CustomerCosts
JOIN CustomerCosts f ON 
  CustomerCosts.CustomerNo = f.CustomerNo AND 
  CustomerCosts.Product = f.Product AND 
  CustomerCosts.Month = f.Month AND 
  CustomerCosts.DataField = f.DataField
WHERE 
  CustomerCosts.FiscalYear = 2014 AND 
  CustomerCosts.DataType = N'Forecast' AND 
  f.FiscalYear = 2014 AND
  f.DataType = N'Budget'

to see whole picture.

Upvotes: 2

Related Questions