SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Calculate		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

servmgr2004
Starting Member

USA
7 Posts
Posted - 12/12/2012 :  13:49:55  Show Profile  Reply with Quote
I have two columns in a table - SHIPLOG
REGION and COPIES. Data is:
0444/1
3176/3
5602/1

I want to write a query that says:

Check SHIPLOG table. If REGION is 0444, then change COPIES of 0444 from 1 to 0 and add COPIES count to 3176.
Create a temp table with:
Insert 3176 into temp table with the new count of 4 and 0444 into temp table with new count of 0.

sunitabeck
Flowing Fount of Yak Knowledge

5152 Posts
Posted - 12/12/2012 :  14:20:42  Show Profile  Reply with Quote
I was with you up to the "....then change COPIES of 0444 from 1 to 0". But I didn't follow what you are saying starting with "and add COPIES count to 3176." Can you explain what that means? What will the table look like after the changes are done?
Go to Top of Page

djj55
Yak Posting Veteran

USA
84 Posts
Posted - 12/12/2012 :  15:14:59  Show Profile  Reply with Quote
What is the logic/criteria that says take value from 0444 and add to 3176?
Go to Top of Page

servmgr2004
Starting Member

USA
7 Posts
Posted - 12/12/2012 :  15:51:49  Show Profile  Reply with Quote
Like this: REGION 3176 has 3 COPIES, REGION 0444 has 1 COPIES. I need the query to look at the table and create a temp table. In the temp table, the query says: IF there is REGION 0444, subtract the number of COPIES AND add it to REGION 3176. In the temp table, you should see:
REGION 3176 now has 4 COPIES, REGION 0444 now has 0 COPIES.
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

3832 Posts
Posted - 12/12/2012 :  15:53:35  Show Profile  Reply with Quote
Here is one way
DECLARE @ShipLog TABLE (REGION VARCHAR(4), COPIES INT)

INSERT @ShipLog VALUES
('0444', 1),
('3176', 3),
('5602', 1)

SELECT *
FROM @ShipLog

-- BEGIN TRAN

UPDATE @ShipLog
SET Copies = Copies + (SELECT COPIES FROM @ShipLog WHERE REGION = '0444')
WHERE REGION = '3176'

UPDATE @ShipLog
SET Copies = 0
WHERE REGION = '0444'

--COMMIT TRAN

SELECT *
FROM @ShipLog
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

3832 Posts
Posted - 12/12/2012 :  15:53:36  Show Profile  Reply with Quote
Here is one way
DECLARE @ShipLog TABLE (REGION VARCHAR(4), COPIES INT)

INSERT @ShipLog VALUES
('0444', 1),
('3176', 3),
('5602', 1)

SELECT *
FROM @ShipLog

-- BEGIN TRAN

UPDATE @ShipLog
SET Copies = Copies + (SELECT COPIES FROM @ShipLog WHERE REGION = '0444')
WHERE REGION = '3176'

UPDATE @ShipLog
SET Copies = 0
WHERE REGION = '0444'

--COMMIT TRAN

SELECT *
FROM @ShipLog
Go to Top of Page

servmgr2004
Starting Member

USA
7 Posts
Posted - 12/12/2012 :  16:03:31  Show Profile  Reply with Quote
The COPIES count, which is an int, varies. It is not always thwe same number, I need it to subtract and add whatever number appears in the COPIES column. The REGION column is always the same.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000