multiple update

Question

multiple update

timotech

SSCrazy

Points: 2698
More actions
November 7, 2010 at 3:38 pm

#228815

Please i have this query:
update convertr$ set Amount = CASE convertr$.lenght WHEN 5 THEN tbl_radiorates.amount WHEN 10 THEN tbl_radiorates.amount WHEN 15 THEN tbl_radiorates.amount WHEN 30 THEN tbl_radiorates.amount
WHEN 45 THEN tbl_radiorates.amount WHEN 60 THEN tbl_radiorates.amount ELSE 5000 END
FROM dbo.convertr$ inner JOIN
dbo.tbl_RadioRates ON SUBSTRING(dbo.convertr$.STA_CODE, 2, 4) = dbo.tbl_RadioRates.FK_StationId AND
dbo.convertr$.LENGHT = dbo.tbl_RadioRates.Duration
WHERE (dbo.convertr$.TIME BETWEEN dbo.tbl_RadioRates.LowerTime AND dbo.tbl_RadioRates.UpperTime)
I'm performing a multiple update to update a table called convertr$ based on a join to another table tbl_radiorates, but in the case else structure, the amount is not set 5000 for those that did not meet the condition, please what can i do?
Thanks

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

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Please post table definitions, sample data and desired output. Read this to see the best way to post this to get quick responses.

http://www.sqlservercentral.com/articles/Best+Practices/61537/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Johan Bijnens SSC Guru Points: 135376 More actions · Answer 2

I would double check this query using a simple select.

Select COUNT(*) nRows

FROM dbo.convertr$

inner JOIN dbo.tbl_RadioRates

ON SUBSTRING(dbo.convertr$.STA_CODE, 2, 4) = dbo.tbl_RadioRates.FK_StationId

AND dbo.convertr$.LENGHT = dbo.tbl_RadioRates.Duration

WHERE ( dbo.convertr$.TIME BETWEEN dbo.tbl_RadioRates.LowerTime

AND dbo.tbl_RadioRates.UpperTime )

and convertr$.lenght not in (5, 10, 15, 30, 45, 60)

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

timotech SSCrazy Points: 2698 More actions · Answer 3

Hi, your query runs well, but u did not put into consideration the else case for the 5000 amount

let me repost the code

update convertr$ set Amount = CASE convertr$.lenght

WHEN 5 THEN tbl_radiorates.amount

WHEN 10 THEN tbl_radiorates.amount

WHEN 15 THEN tbl_radiorates.amount

WHEN 30 THEN tbl_radiorates.amount

WHEN 45 THEN tbl_radiorates.amount

WHEN 60 THEN tbl_radiorates.amount

ELSE 5000

END

FROM dbo.convertr$ inner JOIN

dbo.tbl_RadioRates ON SUBSTRING(dbo.convertr$.STA_CODE, 2, 4) = dbo.tbl_RadioRates.FK_StationId AND

dbo.convertr$.LENGHT = dbo.tbl_RadioRates.Duration

WHERE (dbo.convertr$.TIME BETWEEN dbo.tbl_RadioRates.LowerTime AND dbo.tbl_RadioRates.UpperTime)

Thanks

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Please post table definitions, sample data and desired output. Read this to see the best way to post this to get quick responses.

http://www.sqlservercentral.com/articles/Best+Practices/61537/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

-- Slight rewrite using table aliases and IN to slim-down CASE

UPDATE c

SET Amount = CASE WHEN c.lenght IN (5, 10, 15, 30, 45, 60) THEN r.amount ELSE 5000 END

FROM dbo.convertr$ c

INNER JOIN dbo.tbl_RadioRates r

ON SUBSTRING(c.STA_CODE, 2, 4) = r.FK_StationId

AND c.lenght = r.Duration

WHERE (c.TIME BETWEEN r.LowerTime AND r.UpperTime)

-- Now see if there are any matches which would give Amount = 5000:

-- this is the same as ALZDBA's code

SELECT c.lenght

FROM dbo.convertr$ c

INNER JOIN dbo.tbl_RadioRates r

ON SUBSTRING(c.STA_CODE, 2, 4) = r.FK_StationId

AND c.lenght = r.Duration

WHERE (c.TIME BETWEEN r.LowerTime AND r.UpperTime)

AND c.lenght NOT IN (5, 10, 15, 30, 45, 60)

-- Perhaps you want to update convertr$.amount to 5000

-- where there's no matching row in tbl_RadioRates?

-- Note original WHERE clause would convert LJ to IJ

UPDATE c

SET Amount = CASE WHEN c.lenght IN (5, 10, 15, 30, 45, 60) THEN r.amount ELSE 5000 END

FROM dbo.convertr$ c

LEFT JOIN dbo.tbl_RadioRates r

ON SUBSTRING(c.STA_CODE, 2, 4) = r.FK_StationId

AND c.lenght = r.Duration

AND c.TIME BETWEEN r.LowerTime AND r.UpperTime

-- NOTE: without scripts to create sample tables, code is untested.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden