May 28, 2012 at 9:06 am
Hi i got two tables #users and #a, where i loop through table #a and update table #users.
The code is very simplified but the problem remains.
The problem: I want to loop through table #a and add to the total that is in #users.
When i do it with break points i get right value 1000. Without break points i get 100.
How can i get different results with and without breakpoints to begin with?
Here's the code to understand the problem better, i've left comments where the breakpoint should be placed.
declare @userid CHAR(11)
declare @uweid CHAR(11)
declare @branch CHAR(50)
declare @points INT
declare @points2 INT
BEGIN TRAN
CREATE TABLE #users(id INT PRIMARY KEY, total INT)
INSERT #users VALUES (1,0)
CREATE TABLE #a(id INT PRIMARY KEY, userid INT, points INT)
INSERT #a VALUES (1,1,100)
INSERT #a VALUES (2,1,200)
INSERT #a VALUES (3,1,300)
INSERT #a VALUES (4,1,400)
SELECT id INTO #temp1 FROM #a WHERE userid=1
set rowcount 1
select @uweid=id from #temp1
while @@rowcount <> 0
begin
SELECT @branch=1, @points=points FROM #a WHERE id=@uweid
IF @branch=1
BEGIN
UPDATE #users SET total=total+@points WHERE id=1
--PUT A BREAKPOINT ON THE ROW BELOW
SET @points=0
SELECT @points2=total FROM #users WHERE id=1
--PUT ANOTHER BREAKPOINT ON THE ROW BELOW
END
delete #temp1 where id = @uweid
SET ROWCOUNT 1
select @uweid = id from #temp1
end
drop table #temp1
SELECT * FROM #users WHERE id=1
ROLLBACK
Thank you for your time.
May 28, 2012 at 9:46 am
perhaps this is a better way of achiving what you want to achieve
;with add_points
as
(
select userid,sum(points) as points
from #a
group by userid
)
UPDATE #users SET total=total+points
from #users a inner join add_points b on b.userid=a.id
***The first step is always the hardest *******
May 28, 2012 at 10:18 am
Symbol assignment statements like this:
select @uweid = id from #temp1
will not work the way that you think they do when there is more than one row returned. Try putting a TOP 1 and an ORDER BY on them to stabilize their behavior.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 28, 2012 at 12:39 pm
to be honest i think that its not worth looping through, your better off using the CTE in my previous example to sum the total grouped by ID and use that as the base to make your update rather than doing many iterations of the same update.
***The first step is always the hardest *******
May 29, 2012 at 2:08 am
@RBarryYoung - It did'nt do any difference stabilizing it with ORDER BY and TOP 1, any other ideas?
@SGT_squeequal - The CTE solution looked really clean. Unfortunatly im not so familiar with CTE and the real query is alot more complicated.
@celko - Ty for your reply, it seems that your query wasnt complete, i added "THEN" after "WHEN MATCHED" but still get error "The multi-part identifier "Alpha.user_id" could not be bound."
In SQL Server 2008 there is a debug function with break points, is it not meant to be used with SQL queries?
May 29, 2012 at 2:18 am
Why not add in some DDL and then add what you want to achieve and we can help you. here is a useful ink on CTE http://www.simple-talk.com/sql/t-sql-programming/sql-server-cte-basics/
you could also achieve the same results buy creating a temp table and then using that as your basis to update your records it will be much more efficient than using a loop.
***The first step is always the hardest *******
May 29, 2012 at 3:09 am
It looks like the SET ROWCOUNT setting is reset when you start debugging (ALT-F5, with or without breakpoints). When just running (F5) the script the SET ROWCOUNT setting is not reset. So the SELECT INTO statement inserts just one row into #temp1 when you run the script the second time. Just reset the rowcount setting before the select into:
set rowcount 0
SELECT id INTO #temp1 FROM #a WHERE userid=1
May 29, 2012 at 4:16 am
Ah thanks! Rowcount was indeed the problem, interesting that it resets with debug on.
thanks a bunch.
@SGT_squeequal - ty for the article it was very useful, im working on redoing the query with CTE, wish me luck ^^
May 29, 2012 at 4:21 am
CELKO (5/28/2012)
>> I have two tables Users and Alpha, where I loop through table Alpha and update table Users. <<NO. SQL is a set-oriented declarative language. We do not loop or otherwise mimic mag tapes and Punch cards. Your whole mindset is completely wrong.
We do not create tables inside transactions. We seldom use temp tables. You non-SQL programmers will use them like scratch tapes while we use CTEs and derived tables. There is no concept of a break point in SQL; we work on the whole set all at once, not punch card by punch card. Look up the term ACID transactions properties. Ther is no generic “id”, totals usually computed, not stored and have to be something in particular. I will try to bring your code up to ANSI/ISO Standards basic data modeling.
CREATE TABLE Users
(user_id INTEGER NOT NULL PRIMARY KEY,
penalty_points_tot INTEGER NOT NULL);
INSERT INTO Users VALUES (1, 0);
CREATE TABLE Alpha
(alpha_id INTEGER NOT NULL PRIMARY KEY,
user_id INTEGER NOT NULL
REFERENCES Users(user_id),
penalty_points INTEGER NOT NULL);
INSERT INTO Alpha
VALUES (1, 1, 100),
(2, 1, 200),
(3, 1, 300),
(4, 1, 400);
This one statement will update the total. No loops, no local variables no fake magnetic tapes or punch cards. And it will run 2-3 orders of magnitude faster with fewer resouces.
MERGE INTO Users
USING (SELECT user_id, SUM(penalty_points)
FROM Alpha
GROUP BY user_id)
AS X(user_id, penalty_points)
ON Users.user_id = Alpha.user_id
WHEN MATCHED
UPDATE
SET penalty_points_tot
= penalty_points_tot + X.penalty_points;
Oh dear, seems you have a couple of mistakes in your merge statement.
Try this instead: -
MERGE INTO Users
USING (SELECT user_id, SUM(penalty_points)
FROM Alpha
GROUP BY user_id) AS X(user_id, penalty_points) ON Users.user_id = X.user_id
WHEN MATCHED THEN
UPDATE
SET penalty_points_tot = penalty_points_tot + X.penalty_points;
You see, you missed out the keyword THEN after WHEN MATCHED and because you aliased your source table as X you need to join based on the alias rather than on the table name.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply