Help! - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Help!

Author

Topic

Jezzer
Starting Member

3 Posts

Posted - 2007-10-22 : 07:12:41

Hi, I am new to this forum and would be very grateful of some help if possible?

I am trying to update one table in the database using values from another. Table 1 has the details below, table 2 will also have similar data, which will be updated regularly. What i will want to do is update the "table1.Quantity last 12 months" and "table1.Quantity last 6 months" in table one with the data in table two ("table2.Quantity last 12 months" and "table2.Quantity last 6 months") but only where the "table1.budget Qty" = "table1.Quantity last 12 months" or "table1.Quantity last 6 months"

Table
Customer ID
Shipto ID
Item ID
Warehouse ID
Quantity last 12 months
Quantity last 6 month
Budget Qty

I dont know if this can be done, any help or tips on this would be great. I know I probably haven't explained this too well so if you need any more information plese let me know!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-10-22 : 08:29:00

[code]UPDATE t1
SET [Quantity last 12 months] = t2.[Quantity last 12 months],
[Quantity last 6 MONTH] = t2.[Quantity last 6 MONTH]
FROM table1 t1 INNER JOIN table2 t2
ON t1.[Customer ID] = t2.[Customer ID]
AND t1.[Shipto ID] = t2.[Shipto ID]
AND t1.[Item ID] = t2.[Item ID]
AND t1.[Warehouse ID] = t2.[Warehouse ID]
AND t1.[Budget Qty] = t2.[Quantity last 12 months][/code]

KH
[spoiler]Time is always against us[/spoiler]

Jezzer
Starting Member

3 Posts

Posted - 2007-10-22 : 10:10:41

Thank you for that, much appreciated.

Is it possible to do the following?

What I need to be able to do is update the table one data with the table 2 data, so the quantity last 6 months and last 12months always updates, but where the budget quantity = either the last 6 months or 12 months sales then that too will also need to be updated?

Does that make any sense??

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-10-22 : 10:28:20

[code]UPDATE t1
SET [Quantity last 12 months] = t2.[Quantity last 12 months],
[Quantity last 6 months] = t2.[Quantity last 6 months]
FROM table1 t1 INNER JOIN table2 t2
ON t1.[Customer ID] = t2.[Customer ID]
AND t1.[Shipto ID] = t2.[Shipto ID]
AND t1.[Item ID] = t2.[Item ID]
AND t1.[Warehouse ID] = t2.[Warehouse ID]
AND (
t1.[Budget Qty] = t2.[Quantity last 12 months]
OR t1.[Budget Qty] = t2.[Quantity last 6 months]
)[/code]

KH
[spoiler]Time is always against us[/spoiler]

Jezzer
Starting Member

3 Posts

Posted - 2007-10-22 : 11:18:39

Thanks, yeah that is what I have got already, but what I would like is, if possible (not sure if it is or not!) is that qtylast12mnths and 6 months is always updated no matter what, but the BudgetQty is only update if it = either qtylast12mnths or 6mnths in table 1. So for example, in table1 the underlined “budget Qty” figure will be updated, with the figures in table 2, and the rest will be ignored, so the final table will look like table 1 (After changes)?

Table 1
CustID ShipTo ItemNo WHSID QtyLast12mnths QtyLast6mnths BudgetQty
11004 1 NT00277 T13 36 0 36
11004 1 NT00284 T13 4 0 4
11004 1 TP01391 MA 3 1 3
11004 1 TP05051 RD 20 20 200
11006 1 TP07448 JC 2000 1000 2150
11013 2 TP03284 CU 48 0 0
11013 2 TP03284 ML 14 0 0
11013 2 TP04914 CU 36 26 40
11013 2 TP05051 CU 25 0 25
11013 2 TP05820 CU 2000 1000 2200
11013 2 TP07874 CU 500 500 1200

Table 2
CustID ShipTo ItemNo WHSID QtyLast12mnths QtyLast6mnths
11004 1 NT00277 T13 1 0.5
11004 1 NT00284 T13 2 1
11004 1 TP01391 MA 3 2
11004 1 TP05051 RD 4 3
11006 1 TP07448 JC 5 4
11013 2 TP03284 CU 6 5
11013 2 TP03284 ML 7 6
11013 2 TP04914 CU 8 7
11013 2 TP05051 CU 9 8
11013 2 TP05820 CU 10 9
110013 2 TP007874 CU 11 10

Table 1 (after changes)
CustID ShipTo ItemNo WHSID QtyLast12mnths QtyLast6mnths BudgetQty
11004 1 NT00277 T13 1 0.5 1
11004 1 NT00284 T13 2 1 2
11004 1 TP01391 MA 3 2 3
11004 1 TP05051 RD 4 3 200
11006 1 TP07448 JC 5 4 2150
11013 2 TP03284 CU 6 0 0
11013 2 TP03284 ML 7 0 0
11013 2 TP04914 CU 8 7 92
11013 2 TP05051 CU 9 8 9
11013 2 TP05820 CU 10 9 2200
11013 2 TP07874 CU 11 10 1200

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources