April 20, 2011 at 11:08 am
data:
a b c d
107800 10780 1078 8
107801 10781 1079 9
107802 10781 1079 9
107803 10781 1079
107804 10781 1079
107805 10781 1079
107806 10781 1079
107807 10781 1079
107808 10781 1079
107809 10781 1079
107810 10781 1079
counter for b 10
counter for c 100
counter for d 15000
How would I write an update query (or multiple queries) to column b,c and d so that:
b will increase by 1 (10782) when a becomes 107811 (counter for b > 10)
c will increase by 1 (1080) when the counter for c > 100
d will increase by 1 (10) when the counter for d > 15000
Any help would be greatly appreciated.
thanks.
April 20, 2011 at 11:24 am
You're not making sense here. How does the counter for c increase by 1 when the counter for c increases by 100? Doesn't flow.
Is everything based on column a?
If there's a pattern, then you need to build a math algorithm to match it. So, if you are looking for column b to increase when column a is a multiple of 10, use a modulo (%) operator.
Example:
SELECT Top 205 IDENTITY(INT,1,1) as N
INTO Tally
FROM master.dbo.syscolumns SC1, master.dbo.syscolumns SC2
SELECT n
, n % 10
, CASE WHEN (n % 10) = 0 THEN 'Add 1 to column b' ELSE '' END 'Col b'
FROM Tally
DROP TABLE tally
April 20, 2011 at 11:32 am
Hi Steve, thanks for the suggestion. Sorry, you are correct everything is based on column a (the counter for other columns).
April 20, 2011 at 12:14 pm
I came up with this code:
DECLARE @BUNDLE INT
SELECT @bundle = 10780
SELECT CONTROLNUM,NUMPACK,
case WHEN ((NUMPACK % 10) = 0) THEN
SET @bundle = @bundle + 1
else @bundle
END AS 'NEW BUNDLE'
FROM Cards
WHERE controlNum >= '3774100107801' AND controlNum < '3774100122801'
but then it said incorrect syntax near the keyword set (line 7) and incorrect syntax near the keyword 'else'
April 20, 2011 at 1:42 pm
A few things. First, you cannot use SET inside a SELECT. If this code is going to handle one insert at a time, you would move to an IF statement.
DECLARE @BUNDLE INT
SELECT @bundle = 10780
If (NUMPACK % 10) = 0)
THEN
SET @bundle = @bundle + 1
else @bundle
If you are doing multiple rows, then you need an UPDATE
DECLARE @BUNDLE INT
SELECT @bundle = 10780
UPDATE Cards
SET col_b = @bundle + 1
SET @bundle = @bundle + 1
WHERE controlNum >= '3774100107801' AND controlNum < '3774100122801'
and ((NUMPACK % 10) = 0)
If you are going to try this in a trigger, you'll end up with issues. Every update could potentially cause updates to all rows and you need to code for multiple inserts/updates.
Can I ask for more detail about what you are trying to accomplish here from the business case?
April 21, 2011 at 8:34 am
Thanks Steve for all the help.
April 21, 2011 at 9:53 am
you are welcome. Please post more questions if you need more help with the trigger or update.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply