Help on TSQL update statement

Question

Help on TSQL update statement

s-sql

SSCommitted

Points: 1970
More actions
January 9, 2012 at 10:08 am

#254706

Have a table data as below.
UserID| ObjectID | Cid| Launch| startDate
---------------------------------------------------------------
2026| 17179| 297| 1| 2011-08-31 23:01:00.000
2026| 17179| NULL| 0| 2011-08-31 23:11:46.737
2026| 17179| NULL| 0| 2011-09-12 22:03:51.813
2191| 17172| 578| 1| 2011-09-06 15:45:00.000
2191| 17172| NULL| 0| 2011-09-06 20:18:06.247
The required output is this.
UserID| ObjectID | Cid | Launch | startDate
-------------------------------------------------------
2026| 17179 | 297 | 0 | 2011-08-31 23:01:00.000
2026| 17179 | NULL |1 | 2011-09-10 23:11:46.737
2026| 17179 | NULL | 2 | 2011-09-12 22:03:51.813
2191| 17172 | 578 | 0 | 2011-09-06 15:45:00.000
2191| 17172 | NULL | 1 | 2011-09-07 20:18:06.247
There could multiple records for userid and objectID combo, with different start dates. The launch number needs to be incremented based on the start date for the userid/objectid combo record.
Need help on constructing the update statement to achieve the above resultset.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

Are you familiar with using the ranking functions in T-SQL? Row_Number(), Rank(), Dense_Rank() are the functions.

Build a query that uses those to generate the numbers you need, then join to that and use "Merge Into" or "Update From" (I recommend Merge Into) to update the actual table.

Generally speaking, sequence numbers like that shouldn't be stored in the database. They should be generated at runtime, either in a query or in the user interface. Any reason these ones need to be persisted in the database?

The reason to not store them is because of problems like what you're running into. They have to be changed any time the data changes, and that's a lot of extra work, usually for very little gain.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

stephen99999 Ten Centuries Points: 1197 More actions · Answer 2

In an attempt to help, you could obviously do some sort of +1 if/else statement, or a case when statement to a variable. The below code is very basic and for example purposes only, but gets the general idea across.

@GSquared

I do agree with you. When more appropriate to handle on runtime, do so. Only case I may make, is that we sometimes (rarely) have crazy business requirements that push us from good, standard industry practices.

if/else:

IF ((@UserId = x ) and (@StartDate > y))

BEGIN

UPDATE TableZ SET Launch = Launch + 1 WHERE ID = A

END

Case when:

CASE WHEN @userid = x AND @StartDate > y THEN SET @Launch = Launch + 1

From TableZ

where ID = A

-Stephen

Paul White SSC Guru Points: 150468 More actions · Answer 3

Presumed table structure:

-- This table has no primary key!

DECLARE @Example TABLE

(

UserID integer NOT NULL,

ObjectID integer NOT NULL,

Cid integer NULL,

Launch integer NOT NULL,

StartDate datetime NOT NULL

);

Sample data

INSERT @Example

(UserID, ObjectID, Cid, Launch, StartDate)

VALUES

(2026, 17179, 297, 1, '2011-08-31 23:01:00.000'),

(2026, 17179, NULL, 0, '2011-08-31 23:11:46.737'),

(2026, 17179, NULL, 0, '2011-09-12 22:03:51.813'),

(2191, 17172, 578, 1, '2011-09-06 15:45:00.000'),

(2191, 17172, NULL, 0, '2011-09-06 20:18:06.247');

-- Show the data as it is before the update

SELECT * FROM @Example AS e;

The update script

WITH HowItShouldLook AS

(

-- Number the rows in the right order

SELECT

e.*,

CorrectLaunch =

ROW_NUMBER() OVER (

PARTITION BY e.UserID, e.ObjectID

ORDER BY e.StartDate)

FROM @Example AS e

)

-- Update rows where Launch is wrong

UPDATE HowItShouldLook

SET Launch = CorrectLaunch

WHERE Launch <> CorrectLaunch;

-- Show the result

SELECT * FROM @Example AS eOutput:

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

s-sql SSCommitted Points: 1970 More actions · Answer 4

Thanks for all the replies.

Following query solved my problem.

select b.UserID,b.ObjectID,b.cid,

(select count(*) from @tblexample b2 where b.UserID = b2.UserID and b.ObjectID = b2.ObjectID

and b2.StartDate < b.StartDate) as Launch,

b.StartDate from @tblexample b

@GSquared

I do agree with you. The sequence generation is handled during run time.

However,this script was a part of patch fix, to clean the existing data.