Calculate - 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 2008 Forums

Transact-SQL (2008)

Calculate

Author

Topic

servmgr2004
Starting Member

10 Posts

Posted - 2012-12-12 : 13:49:55

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
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-12-12 : 14:20:42

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?

djj55
Constraint Violating Yak Guru

352 Posts

Posted - 2012-12-12 : 15:14:59

What is the logic/criteria that says take value from 0444 and add to 3176?

servmgr2004
Starting Member

10 Posts

Posted - 2012-12-12 : 15:51:49

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.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-12-12 : 15:53:35

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

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-12-12 : 15:53:36

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

servmgr2004
Starting Member

10 Posts

Posted - 2012-12-12 : 16:03:31

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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources