Update rows without using a cursor

Question

Update rows without using a cursor

BrianBurgit

SSChasing Mays

Points: 650
More actions
November 24, 2006 at 12:37 pm

#121673

I’m trying to figure out how to do the following without a cursor. I have a Table as follows:

EventDate
ValidPurchaseDateStart
ValidPurchasedateEnd
Price

I can have multiple rows for the same EventDate because the PurchaseDate ranges can be different, like so:

EventDate = 6/1/2007
ValidPurchaseDateStart = 1/1/2006
ValidPurchasedateEnd = 6/30/2006
Price = 100

EventDate = 6/1/2007
ValidPurchaseDateStart = 7/1/2006
ValidPurchasedateEnd = 5/31/2007
Price = 200

Now I may need to update the rows such that my final data looks like this

EventDate = 6/1/2007
ValidPurchaseDateStart = 1/1/2006
ValidPurchasedateEnd = 3/31/2006
Price = 100

EventDate = 6/1/2007
ValidPurchaseDateStart = 4/1/2006
ValidPurchasedateEnd = 12/31/2006
Price = 150

EventDate = 6/1/2007
ValidPurchaseDateStart = 1/1/2007
ValidPurchasedateEnd = 5/31/2007
Price = 200

I would like to write a SP where I pass in the new data and figure out what to do – in this case I need to change the ValidPurchasedateEnd on the first row, INSERT a new row for the new data, and change the ValidPurchasedateStart on the second row. I can write the logic for this, but am thinking I need to SELECT all rows for this EventDate first, then loop thru them one by one and determine what action to take with that row. Can I do this looping without a cursor?

Thanks,

Brian

Viewing 8 posts - 1 through 7 (of 7 total)

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

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 1

I'm not seeing the pattern here... can you explain how you came to the actions you took and why you need to do the INSERT to begin with? What do you need to change the ValidPurchaseEnd to on the first row? Is it based on some criteria or just some unpredictable manual entry?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

BrianBurgit SSChasing Mays Points: 650 More actions · Answer 2

The idea is that a user would be using a data admin program. There could be several different scenarios I would need to handle, depending on the changes the user makes.

Sergiy SSC Guru Points: 110208 More actions · Answer 3

Still does not make sense.

_____________
Code for TallyGenerator

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 4

I agree... there's still some info missing...

Brian, are you simply saying that you want to be able to update any row and insert new rows from the app?

Or are you saying that some sort of magic is supposed to occur automatically to change the 2 rows to 3?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

BrianBurgit SSChasing Mays Points: 650 More actions · Answer 5

Imagine buying a seat on an airplane for travel on 6/1/2007. If you purchase your seat between 1/1/2006 and 6/30/2006 the price is $100, and if you purchase between 7/1/2006 and 5/31/2007 the price is $200. I would have 2 rows in my Table, like the following

EventDate = 6/1/2007

ValidPurchaseDateStart = 1/1/2006

ValidPurchasedateEnd = 6/30/2006

Price = 100

EventDate = 6/1/2007

ValidPurchaseDateStart = 7/1/2006

ValidPurchasedateEnd = 5/31/2007

Price = 200

Now suppose the airline wants to change the pricing via some administration program such that the price of the seat is $150 if you purchase between 4/1/2006 and 12/31/2006. I would need to end up having 3 rows, like this. I would need to change the date range on the first and second, already exsiting rows, and insert 1 new row. I hope this is clear.

EventDate = 6/1/2007

ValidPurchaseDateStart = 1/1/2006

ValidPurchasedateEnd = 3/31/2006

Price = 100

EventDate = 6/1/2007

ValidPurchaseDateStart = 4/1/2006

ValidPurchasedateEnd = 12/31/2006

Price = 150

EventDate = 6/1/2007

ValidPurchaseDateStart = 1/1/2007

ValidPurchasedateEnd = 5/31/2007

Price = 200

Sergiy SSC Guru Points: 110208 More actions · Answer 6

CREATE PROCEDURE dbo.SetNewPurchasePeriod

@DateStart smalldatetime, @DateEnd smalldatetime, @Price money

AS

DELETE FROM TABLE

WHERE ValidPurchaseDateStart BETWEEN @DateStart AND @DateEnd

AND ValidPurchaseDateEnd BETWEEN @DateStart AND @DateEnd

INSERT INTO TABLE (ValidPurchaseDateStart, ValidPurchasedateEnd, Price)

SELECT ValidPurchaseDateStart, @DateEnd -1, Price

FROM Table

WHERE ValidPurchaseDateStart < @DateStart AND ValidPurchasedateEnd > @DateEnd

UNION

SELECT @DateStart + 1 , ValidPurchasedateEnd, Price

FROM Table

WHERE ValidPurchaseDateStart < @DateStart AND ValidPurchasedateEnd > @DateEnd

DELETE FROM Table

WHERE ValidPurchaseDateStart < @DateStart AND ValidPurchasedateEnd > @DateEnd

UPDATE Table

SET ValidPurchaseDateEnd = @DateStart -1

WHERE ValidPurchaseDateStart < @DateStart

AND ValidPurchaseDateEnd BETWEEN @DateStart AND @DateEnd

UPDATE Table

SET ValidPurchaseDateStart = @DateEnd + 1

WHERE ValidPurchaseDateStart BETWEEN @DateStart AND @DateEnd

AND ValidPurchaseDateEnd > @DateEnd

INSERT INTO Table (ValidPurchaseDateStart, ValidPurchasedateEnd, Price)

SELECT @DateStart, @DateEnd, @Price

GO

_____________
Code for TallyGenerator

BrianBurgit SSChasing Mays Points: 650 More actions · Answer 7

Thanks Sergiy, this works and is a different and more efficient approach than I had been thinking of.