April 29, 2009 at 3:51 pm
Hi, i trying to an update statement but can´t get it to work.
Here is the senario.
table1
- seq // auto increment int
- name // varchar
- cat // varchar
- number // varchar null
I want to update the number column like an autoincrement column but i like to choose what numbers to put in. So depending of what the cat field value is i want to update the number field.
For example if the cat field has the value "something1" i want to update the name field like this
10
11
12
etc..
and the same goes if the cat field value is "something2"
30
31
32
But first of all i want this little code to work below, this is just a simple counter for the number field. This doesn´t work at all and i get an "syntax error message"
DECLARE @counter int
SET @counter = 1
UPDATE custom.table1
SET @counter = table1.number = @counter+1
If i make the changes below i´ll get the following result.
1
1
1
1
DECLARE @counter int
SET @counter = 0
UPDATE custom.table1
SET @counter = table1.number = @counter+1
I´m i missing someting or is this the wrong way to solve it.
Regards
Magnus
April 29, 2009 at 7:07 pm
This logic would require some kind of looping to work. You also need a where clause in your update. Probably use a subselect for your incrementing?
give us some more specific table definitions and we can help...
April 29, 2009 at 8:18 pm
The only difference I see between your two code snippets is starting @counter at 0 or 1. I don't see how that would make a difference. Meanwhile, You don't need a loop to have SQL count up and create incremented numbers -- you were close to that with your code using local variable @counter. The twist in your case is setting different ranges for different categories.
To update all rows with incremented values, here's some sample code to do that:drop Table #custTable
create Table #custTable (number int, cat char(1) )
insert #custTable values (0,'A')
insert #custTable values (0,'A')
insert #custTable values (0,'B')
insert #custTable values (0,'B')
insert #custTable values (0,'C')
insert #custTable values (0,'C')
insert #custTable values (0,'C')
DECLARE @counter int
SET @counter = 0
UPDATE #custTable
Set @counter = number = @counter+1
Select * from #custtable
For use on a table which has had rows added, determine your starting point
and add a WHERE clause to limit the update to just those rows that need it.
select @counter=max(number)
from #custtable
UPDATE #custTable
Set @counter = number = @counter+1
Where number = 0
For your original problem, if you have a small number of categories, it may be just as well to run a separate update for each: DECLARE @counter int
select @counter=30 -- starting value for this category
-- from #custtable
--where cat = 'A'
UPDATE #custTable
Set @counter = number = @counter+1
where cat = 'A'
select @counter=90 -- starting value for this category
-- from #custtable
--where cat = 'B'
UPDATE #custTable
Set @counter = number = @counter+1
where cat = 'B'
Select * from #custtable
Now, if we're lucky, someone can help us figure out a single pass solution.
-------------
Edit to comment out extraneous FROM clauses in last code section.
April 29, 2009 at 8:35 pm
john.arnott (4/29/2009)
Now, if we're lucky, someone can help us figure out a single pass solution.
Solution was posted here many times, and Jeff Moden even made an article out of his posts.
You can find it here:
Solving the "Running Total" & "Ordinal Rank" Problems[/url]
_____________
Code for TallyGenerator
April 29, 2009 at 8:45 pm
Sergiy (4/29/2009)
john.arnott (4/29/2009)
Now, if we're lucky, someone can help us figure out a single pass solution.Solution was posted here many times, and Jeff Moden even made an article out of his posts.
You can find it here:
The code from the original article is still there and is still viable (no including the ORDER BY solution). I'm in the process of writing the article.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 29, 2009 at 8:54 pm
m.berggren (4/29/2009)
DECLARE @counter int
SET @counter = 1
UPDATE custom.table1
SET @counter = table1.number = @counter+1
The problem with that is that you've included the table schema in the set clause. It should be...
DECLARE @counter int
SET @counter = 0
UPDATE custom.table1
SET @counter = number = @counter+1
... and don't forget that no matter what you do, the count will be in the same order as the clustered index. If there is no clustered index, the no guarantee's on what the order will be. Also, I fixed the counter. It should be preset to 0 if you want the count to start at 1.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 29, 2009 at 8:56 pm
john.arnott (4/29/2009)
Now, if we're lucky, someone can help us figure out a single pass solution.
Use two counters and a case statement in the SET.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 29, 2009 at 8:58 pm
Bob Griffin (4/29/2009)
This logic would require some kind of looping to work. You also need a where clause in your update. Probably use a subselect for your incrementing?give us some more specific table definitions and we can help...
An UPDATE statement is a loop... behind the scenes. Just like a SELECT is actually a loop behind the scenes. We call them "pseudo-cursors".
--Jeff Moden
Change is inevitable... Change for the better is not.
April 30, 2009 at 1:40 am
I get back to you all in a couple of hours with an repply, have some work to do now 🙂 .....
Thx
April 30, 2009 at 7:21 am
Hi, i tried this one but i got some syntax errors again. It´s complaining about the "=" in row 4. Maybe beacuse this is written in Sybase (interactive sql) instead of SQL server ? Thought the syntax should be the same, rookie here 🙂 ..
Does it matter if the number filed is an varchar or numeric ?
DECLARE @counter int
SET @counter = 0
UPDATE custom.table1.
SET @counter = number = @counter+1
Regards
Magnus
April 30, 2009 at 10:16 am
m.berggren (4/30/2009)
Hi, i tried this one but i got some syntax errors again. It´s complaining about the "=" in row 4. Maybe beacuse this is written in Sybase (interactive sql) instead of SQL server ? Thought the syntax should be the same, rookie here 🙂 ..Does it matter if the number filed is an varchar or numeric ?
DECLARE @counter int
SET @counter = 0
UPDATE custom.table1.
SET @counter = number = @counter+1
Regards
Magnus
Dunno if that's the actual actual problem, but you have an extra "dot" in the same line as the UPDATE.
Also, this type of "quirky" update originated in SyBase, so it should work. Any chance of you posting the UPDATE statement syntax from the Sybase documentation so that I can be sure?
--Jeff Moden
Change is inevitable... Change for the better is not.
April 30, 2009 at 10:18 am
DECLARE @counter int
SET @counter = 0
UPDATE custom.table1
SET @counter = table1.number = @counter+1
Also, since you got that to execute previously, you may have to add an "anchor" column and it should also be a part of the clustered index.
DECLARE @counter int
DECLARE @Dummy somedatatype
SET @counter = 0
UPDATE custom.table1
SET @counter = table1.number = @counter+1,
@Dummy = someothercolumn
--Jeff Moden
Change is inevitable... Change for the better is not.
April 30, 2009 at 12:34 pm
Well, I spent more time than I should have trying to get the cascaded value assignments (@counter = column = @counter + 1) to work inside a case statement. Finally the lightbulb went on and I split them out. Here's a solution that increments each of the separate range counters with its own case statement, then uses another case statement to choose which counter is appropriate.
I hope this test data helps you (m.berrggren) towards an answer with your tables.
Jeff,
Is there a more efficient solution? I haven't tried it in SQL 2005, but figure a solution on that platform could be developed using row_number(). But this is SQL 2000.....
drop Table #custTable
create Table #custTable (number int, cat char(1) )
insert #custTable values (0,'A')
insert #custTable values (0,'A')
insert #custTable values (0,'B')
insert #custTable values (0,'B')
insert #custTable values (0,'C')
insert #custTable values (0,'C')
insert #custTable values (0,'C')
insert #custTable values (0,'A')
insert #custTable values (0,'A')
insert #custTable values (0,'B')
insert #custTable values (0,'B')
insert #custTable values (0,'C')
insert #custTable values (0,'C')
insert #custTable values (0,'C')
DECLARE @counterA int
select @counterA=30 -- starting value for this category
DECLARE @counterB int
select @counterB=90 -- starting value for this category
UPDATE #custTable
Set @counterA = case when cat = 'A' then @counterA + 1
else @counterA
end
,@counterB = case when cat = 'B' then @counterB + 1
else @counterB
end
,number = casewhen cat = 'A' then @counterA
when cat = 'B' then @counterB
else number
end
select * from #custTable
April 30, 2009 at 1:17 pm
Jeff:
How does this so called "anchor" field works ?
Here are a helpful link about statements in SQL Anywhere Studio 9.0.2
http://www.ianywhere.com/developer/product_manuals/sqlanywhere/0902/en/html/index.html
Regards
Magnus
May 1, 2009 at 9:17 pm
john.arnott (4/30/2009)
But this is SQL 2000.....
I thought you said you were using SyBase.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 39 total)
You must be logged in to reply to this topic. Login to reply