October 18, 2011 at 3:45 pm
Hi I had a problem for updating a row according to previous row.
My table looks like this:
ID COUNT DATE
1 22 6/2/2011
1 NULL 6/3/2011
and Result should be updated like following
ID COUNT DATE
1 22 6/2/2011
1 22 6/3/2011
I am using queries:
update A
set
[COUNT] = (
select [COUNT]
from A as T
where T.id = A. id
and T.Date < A.Date
)
go
The problem is when I run the query, the result turns out like this:
ID COUNT DATE
1 null 6/2/2011
1 22 6/3/2011
October 18, 2011 at 3:54 pm
Look at the following thread for a nice example on the quirky update.
http://qa.sqlservercentral.com/Forums/Topic936773-338-1.aspx
This should help with what you need.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
October 18, 2011 at 4:07 pm
Another option is to use ROW_NUMBER() to assign positional indicators to the records via a CTE, then link them up on id = id - 1. Ends up being a bit less complex if you're not actually doing running totals.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
October 19, 2011 at 8:02 am
Hi CELKO,
Sorry for the confusion, please see DDL following and I am new to SQL, so I don't really understand how to stored procedure will correct the NULL mistake which is generated by updating the table.
CREATE TABLE [dbo].[STAT](
[ID] [int] NOT NULL,
[COUNT] [int] NULL,
[Date] [datetime] NULL
) ON [PRIMARY]
October 19, 2011 at 1:09 pm
Evil Kraig F (10/18/2011)
Another option is to use ROW_NUMBER() to assign positional indicators to the records via a CTE, then link them up on id = id - 1. Ends up being a bit less complex if you're not actually doing running totals.
ROW_NUMBER works, thank you.
With CTE
As
(
Select
ID
,[count]
,[date]
,Row_Number() Over(Partition by VM_ID Order by [date]) As RN
From dbo.VMDLYPWRDSTAT1
)
update CTE
set
[count] = (
Select top 1 MainQry.[count]
from CTE As MainQry
Left Outer Join CTE As SubQry
On MainQry.ID = SubQry.ID And MainQry.RN = SubQry.RN - 1
where MainQry.[date] < SubQry.[date]
)
go
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply