May 1, 2009 at 9:20 pm
m.berggren (4/30/2009)
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
Matt Miller did some testing and claimed that without it, SQL Server 2005 sometimes loses it's mind on the Quirky Update and that settles it down because it only has a single operand instead of two.
I've not tested it myself... Matt is one of those folks I've tested with in the past and I pretty much take his word for it when he says he's ferreted something out. Probably not a good habit for me to get into, but I trust Matt, a lot.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 1, 2009 at 9:25 pm
john.arnott (4/30/2009)
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 #custTablecreate 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
The problem is, you haven't created the table correctly. In order for the quirky update to operate properly, the table absolutely MUST have a clustered index in the order that you want the update to be done. The problem with your data is, you have nothing to include in the clustered index that will maintain the proper order.
Even with the code that you have in the quote above, there is no guarantee that it will work because you have nothing to guarantee the update order.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 2, 2009 at 12:18 am
Jeff,
Sorry for any confusion; I'm not the original poster. He posted in the SQL 2000 forum with just a snippet of code -- no table def's, nothing to go on. He may be doing his work on Sybase, but I took the forum as an indication of the feature set available -- I do not have Sybase.
Since he hadn't provided one, I made up the sample table to at least try to point him in the right direction in terms of partitioning the data. I was aware that I wasn't specifying an order within the "cat" partition, figuring that since the OP hadn't specified one just yet, I'd let him at least figure out how to separate the different groups.
I think from now on in this thread, I'll let the OP (m.berggren) provide the details of what his data really looks like and let us see what he's done with the information he's received so far.
May 2, 2009 at 3:25 am
Here is the code when i create the table. The fields are filled with values from a text file.
Maybe there is something wrong when I create the table ?
CREATE TABLE custom.citems (
mi_seq INT PRIMARY KEY NOT NULL DEFAULT AUTOINCREMENT,
number INT,
cat CHAR (16),
name CHAR (50)
);
INPUT INTO custom.citems FROM C:\items.txt format ASCII (number, cat, name);
Regards
Magnus
May 2, 2009 at 10:55 am
john.arnott (5/2/2009)
Jeff,Sorry for any confusion; I'm not the original poster. He posted in the SQL 2000 forum with just a snippet of code -- no table def's, nothing to go on. He may be doing his work on Sybase, but I took the forum as an indication of the feature set available -- I do not have Sybase.
Since he hadn't provided one, I made up the sample table to at least try to point him in the right direction in terms of partitioning the data. I was aware that I wasn't specifying an order within the "cat" partition, figuring that since the OP hadn't specified one just yet, I'd let him at least figure out how to separate the different groups.
I think from now on in this thread, I'll let the OP (m.berggren) provide the details of what his data really looks like and let us see what he's done with the information he's received so far.
That's what I get for posting without being under the influence of caffeine. I apologize for the mistaken identity, John. :blush:
--Jeff Moden
Change is inevitable... Change for the better is not.
May 2, 2009 at 10:59 am
m.berggren (5/2/2009)
Here is the code when i create the table. The fields are filled with values from a text file.Maybe there is something wrong when I create the table ?
CREATE TABLE custom.citems (
mi_seq INT PRIMARY KEY NOT NULL DEFAULT AUTOINCREMENT,
number INT,
cat CHAR (16),
name CHAR (50)
);
INPUT INTO custom.citems FROM C:\items.txt format ASCII (number, cat, name);
Regards
Magnus
Again, this is Sybase, so I'm double checking because I just don't know for sure... does SyBase automatically make a clustered index as the default index for primary keys?
--Jeff Moden
Change is inevitable... Change for the better is not.
May 5, 2009 at 1:28 am
Jeff:
I think youré right. I think it has something to with clustered index on the table.
// Magnus
May 5, 2009 at 6:04 pm
m.berggren (5/5/2009)
Jeff:I think youré right. I think it has something to with clustered index on the table.
// Magnus
It absolutely does... UPDATE will update the data in the order of the clustered index. That's why they call it the "quirky update". Change your insert to include an ORDER BY for the correct order that the data should be processed in and I believe the PK on the autoincrement column will also be the default clustered index. Since that will be in the correct order (there is one exception which I don't believe applies here) because of the ORDER BY in the INSERT, you should be ok)
--Jeff Moden
Change is inevitable... Change for the better is not.
May 6, 2009 at 4:34 am
Finally i got i to work!
Was searching the internet back and forward regarding this clustered index issue, found a lot of information and tried a lot without any luck.
Then i changed the code you wrote Jeff and to the following. Little unsecure why it´s working now but is has something to do with changing from SET @counter = number = @counter+1 to set number=number+@counter, @counter=@counter+1;
declare @counter int
select @counter=0
update custom.table1
set number=number+@counter, @counter=@counter+1;
// Magnus
May 6, 2009 at 7:34 am
I almost got this CASE update statement to work now for the catagory field. But the strange thing is that it´s only update´s cat "s1" like this
cat "s1"
10
11
12
etc..
cat "s2"
40
40
40
etc..
If you remove the line WHERE t1.cat IN ('s1','s2') it doesn´t update at all.
UPDATE custom.t1
SET citems.number = '0'
DECLARE @i1 int, @i2 int
SELECT @i1=10, @i2=40
UPDATE custom.t1
SET number=
CASE
WHEN
t1.cat='s1'
THEN
num+@i1
WHEN
t1.cat='s2'
THEN
num+@i2
END
,@i1=
CASE
WHEN
t1.cat='s1'
THEN
@i1+1
END
,@i2=
CASE
WHEN
t1.cat='s2'
THEN
@i2+1
END
WHERE t1.cat IN ('s1','s2')
So, how come it´s only updating catagory "s1" ?
Regards
Magnus
May 7, 2009 at 5:16 am
Hi,
The code below generates the following result.
declare @i1 int
declare @i2 int
select @i1=10
select @i2=40
update custom.t1
set num=
case
when
t1.cat='s1'
then
number+@i1
when
t1.cat='s2'
then
nummber+@i2
end
Result:
10
10
10
40
40
40
etc ..
But if i change it to
declare @i1 int
declare @i2 int
select @i1=10
select @i2=40
update custom.t1
set num=
case
when
t1.cat='s1'
then
number+@i1
when
t1.cat='s2'
then
nummber+@i2
end,
@i1=@i1+1,
@i2=@i2+1
Result:
10
11
12
61
62
63
etc ..
How come that the second CASE is starting at 60 when the variable is set to start at 40 ?
Regards
Magnus
May 7, 2009 at 9:22 am
The answer is in your "etc.". That is, you didn't show all the results, but I can see that regardless of whether you updated an s1 or s2 row, you've incremented both counters every time.
May 7, 2009 at 11:40 am
Old Hand:
Well, "etc" isn´t a part of the result actually. I just wrote it to mark a stop in the result of the query. So don´t mind about the "etc" .. 🙂 .. just to demostrate the output.
May 7, 2009 at 12:09 pm
Grasshopper,
Sorry to confuse you. By saying the answer is in the "etc.", I was trying to get you to look at the overall process. Your actual results would have shown about 20 results in the 10 range (10, 11, 12...).
The second half of my response points out the reason the S2 rows start at 61: " regardless of whether you updated an s1 or s2 row, you've incremented both counters every time. "[/size]
You may want to take another look at the code I posted for you Posted 4/30/2009 7:34:23 PM:
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
May 7, 2009 at 2:25 pm
Old Hand:
Thx, i just tried youré code snippet and it don´t work. Well, it works but it doesn´t give right the output. The result is:
number, cat
31, A
31, A
91, B
91, B
0, C
0, C
0, C
31, A
31, A
91, B
91, B
0, C
0, C
0, C
I think i has something to do how the syntax is written. Beacuse if i change it to the code below it work´s fine. So there has to something wrong whitin the case statement. If i cut out the CASE statement and instead write:
UPDATE custom.custTable
set number=number+@counterA, @counterA=@counterA+1
where custTable.cat = 'A'
UPDATE custom.custTable
set number=number+@counterB, @counterB=@counterB+1
where custTable.cat = 'B'
/Magnus
Viewing 15 posts - 16 through 30 (of 39 total)
You must be logged in to reply to this topic. Login to reply