January 17, 2011 at 10:52 am
Here's the scenario:
There are two tables involved. One is the BookingActivity table which will be updated. The Update statement will also look at the Clients table to evaluate what value the BookingActivity table should be updated with. The faretypeid in the clients table can be one of 5 possible values (7, 8, 9, 10, 11). If it's value is 9, 10 or 11, then update the BookingActivity faretypeid to 4. If it's anything else (ie 7 or 8) then update the bookingactivity Faretypeid to that same value. Here's what I tried to put together for that:
UPDATE BookingActivity
SET faretypeid =
CASE WHEN c.FareTypeId IN (9,10,11) THEN 4
ELSE faretypeid = b.FareTypeId
END
FROM Booking b
LEFT JOIN Clients c ON b.ClientID = c.ClientID
LEFT JOIN BookingLegs bl on bl.bookingid = b.bookingid
LEFT JOIN BookingActivity ba on ba.legid = bl.legid and ba.passtype = 'CLI'
WHERE...
Does this look right???
And a follow up question to that, if it is right, would I also be able to update a few other fields at the same time (in the same update statement) or do i have to do it separately? Like this:
UPDATE BookingActivity
SET faretypeid =
CASE WHEN c.FareTypeId IN (9,10,11) THEN 4
ELSE faretypeid = b.FareTypeId
END
, FareAmount = ft.FlatRate
, FundingsourceID = b.FundingsourceID
FROM Booking b
LEFT JOIN Clients c ON b.ClientID = c.ClientID
LEFT JOIN BookingLegs bl on bl.bookingid = b.bookingid
LEFT JOIN BookingActivity ba on ba.legid = bl.legid and ba.passtype = 'CLI'
LEFT JOIN FareTypes ft on b.FareTypeID = ft.FareTypeID
WHERE...
Help would be most appreciated!
Thanks
January 17, 2011 at 11:00 pm
tacy.highland (1/17/2011)
Here's the scenario:There are two tables involved. One is the BookingActivity table which will be updated. The Update statement will also look at the Clients table to evaluate what value the BookingActivity table should be updated with. The faretypeid in the clients table can be one of 5 possible values (7, 8, 9, 10, 11). If it's value is 9, 10 or 11, then update the BookingActivity faretypeid to 4. If it's anything else (ie 7 or 8) then update the bookingactivity Faretypeid to that same value. Here's what I tried to put together for that:
UPDATE BookingActivity
SET faretypeid =
CASE WHEN c.FareTypeId IN (9,10,11) THEN 4
ELSE
faretypeid =b.FareTypeIdEND
FROM Booking b
LEFT JOIN Clients c ON b.ClientID = c.ClientID
LEFT JOIN BookingLegs bl on bl.bookingid = b.bookingid
LEFT JOIN BookingActivity ba on ba.legid = bl.legid and ba.passtype = 'CLI'
WHERE...
Does this look right???
No... I've struck out the code that will cause an error in the above.
Don't mind Celko too much on the naming thing... he doesn't believe in the use of ID columns if a natural key is available. He also doesn't like proprietary stuff like the UPDATE/FROM which is mostly proprietary code.
On the flip side of the coin, he is correct that MERGE should probably become second nature to everyone even if it's being used just as an UPDATE for the very reasons he mentioned (except when I want to build in a cardinality violation, of course! 😉 ).
--Jeff Moden
Change is inevitable... Change for the better is not.
January 18, 2011 at 8:47 am
Thanks for the replies.
I'm a little nervous on the MERGE code since I'm not familiar with it. Can i assume that, while not best practice, the UPDATE FROM is still a valid method of accomplishing what i need for now? (I'd rather get this taken care of (deadlines looming!) and then be able to dig in and take a closer look at MERGE after-the-fact.)
Also, on the part of the code that was removed, how do i specify that if the client faretype is something other than 9, 10, or 11 then to change the bookingactivity faretype to whatever is listed in the booking table? Do i need to do a separate UPDATE statement to catch those which are not 9, 10, or 11?
January 18, 2011 at 1:19 pm
tacy.highland (1/17/2011)
Here's the scenario:
UPDATE BookingActivity
SET faretypeid =
CASE WHEN c.FareTypeId IN (9,10,11) THEN 4
ELSE faretypeid = b.FareTypeId
END
FROM Booking b
LEFT JOIN Clients c ON b.ClientID = c.ClientID
LEFT JOIN BookingLegs bl on bl.bookingid = b.bookingid
LEFT JOIN BookingActivity ba on ba.legid = bl.legid and ba.passtype = 'CLI'
WHERE...
Does this look right???
It would look ok if you would replace all LEFT JOINs with INNER JOIN (i like to ommit optional word INNER and just type JOIN).
I would use "UPDATE ba" instead of "UPDATE BookingActivity".
But, nevermind the sql. More important thing is the logic of the business process: is it sound ? Is such data flow logical ?
You are deducing information, creating redundancy. By this code you do not enter any new information into the system - it can be derived from other data. So it can be replaced with a vew, eliminating update totally. Controlled redundancy can be there for performance reasons (faster selects at cost of preparing and maintaining redundancy).
would I also be able to update a few other fields at the same time (in the same update statement)
If this UPDATE command involves all the rows you want to change, then yes, you can update other columns too - it is preferred way and single command will perform faster than several separate update commands.
January 18, 2011 at 1:32 pm
Vedran brings up a term that I hadn't considered: REPLACE. Since, as it was pointed out, I'm not necessarily adding new information, just replacing a value with another existing value in another table, is a REPLACE statement the best way to go on this? Would I still be able to do the CASE statement as specified in the beginning?
Anyone out there have any other answers that might be helpful? I'm growing more and more desperate to figure this out....
January 18, 2011 at 2:24 pm
tacy.highland (1/18/2011)
Vedran brings up a term that I hadn't considered: REPLACE. Since, as it was pointed out, I'm not necessarily adding new information, just replacing a value with another existing value in another table, is a REPLACE statement the best way to go on this? Would I still be able to do the CASE statement as specified in the beginning?
REPLACE does something very very different. It's for manipulating the interior of a string to swap in one pattern when another pattern is found.
I would continue with the UPDATE / CASE statement as you seem closest to understanding that syntax, Celko's ranting aside.
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
January 18, 2011 at 9:08 pm
tacy.highland (1/18/2011)
Thanks for the replies.I'm a little nervous on the MERGE code since I'm not familiar with it. Can i assume that, while not best practice, the UPDATE FROM is still a valid method of accomplishing what i need for now? (I'd rather get this taken care of (deadlines looming!) and then be able to dig in and take a closer look at MERGE after-the-fact.)
It's probably time to get over any nervousness with MERGE. As Joe will be quick to point out, a group of MVP's are petitioning heavily to get rid of UPDATE/FROM. I personally hope they're not successful because sometimes I want UPDATE/FROM to do the "incorrect" thing.
Also, on the part of the code that was removed, how do i specify that if the client faretype is something other than 9, 10, or 11 then to change the bookingactivity faretype to whatever is listed in the booking table? Do i need to do a separate UPDATE statement to catch those which are not 9, 10, or 11?
No... not if you want other than 9, 10, and 11 to stay the same as I believe your code intends. The change I made should work. For the joins the others are talking about, I've not given those a real close look because they weren't my primary focus. The bad CASE statement was.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 19, 2011 at 1:51 pm
Hey thank you Jeff. I really appreciate your help on this, and it's exactly the CASE statement that I was looking for assistance with so thank you again...
On the last part, no, the records which are NOT 9, 10, or 11 will NOT stay the same, they should be updated using the values in the booking table. This is why the update statement specifies "ELSE faretypeid = b.faretypeid". Now when I have the code entered as I originally entered it in this forum:
UPDATE BookingActivity
SET faretypeid =
CASE WHEN c.FareTypeId IN (9,10,11) THEN 4
ELSE faretypeid = b.FareTypeId
END
,FareAmount = ft.FlatRate
,SmartCardID = c.SmartCardID
FROM Booking b
LEFT JOIN Clients c ON b.ClientID = c.ClientID
LEFT JOIN BookingLegs bl on bl.bookingid = b.bookingid
LEFT JOIN BookingActivity ba on ba.legid = bl.legid and ba.passtype = 'CLI'
LEFT JOIN FareTypes ft on b.FareTypeID = ft.FareTypeID
WHERE ...
I get the "red squigglies" in a few different places:
- Under the c.FaretypeID of "WHEN c.FaretypeID IN..." stating that it could not be bound (but we can see the c alias is defined in the FROM statement...???)
- Under the = in the "ELSE faretypeid = b.faretypeid" stating incorrect syntax
- Under the , with the additional fields in the SET statement ", FareAmount = ft.FlatRate..." stating incorrect syntax, expecting CONVERSATION (what is that??)
So there are a few issues it seems with the format here. I have a feeling that the ELSE statement must set the value to something simple, ie it can't get the ELSE value from another table, and if that's the case I'm worried that I'll have to do this update statement as you specified, leaving the "ELSE" values which are not 9, 10, 11 as they are and then follow up with ANOTHER update statement that specifies what to do with the other values, (which should be set to the values in the booking table).
Goodness. I hope I haven't gotten too complicated for this. Am I making any sense (or do i just sound like i am in my own head???). hehe
Thanks again
January 19, 2011 at 2:44 pm
CASE in not complex at all.
I already wrote you description of what might be wrong.
You cannot use LEFT JOIN and then update right table - if there is no matched row you cannot update nonexisting row. You can update only LEFT table of left join, or right table of right join.
Use prefix, not the table name in UPDATE clause.
Use prefix before column name you update (and generally, use prefixes everywhere, it's a good habit).
Within the CASE statement you cannot assign a value. CASE itself is an expression that evaluates to a value.
Here is a corrected version (didn't try it because i have no create table script for your tables, this is straight from the mind):
UPDATE ba
SET ba.faretypeid =
CASE
WHEN c.FareTypeId IN (9,10,11) THEN 4
ELSE b.FareTypeId
END,
ba.FareAmount = ft.FlatRate,
ba.SmartCardID = c.SmartCardID
FROM Booking b
JOIN Clients c ON b.ClientID = c.ClientID
JOIN BookingLegs bl on bl.bookingid = b.bookingid
JOIN BookingActivity ba on ba.legid = bl.legid and ba.passtype = 'CLI'
LEFT JOIN FareTypes ft on b.FareTypeID = ft.FareTypeID
WHERE ...
January 20, 2011 at 3:22 pm
Thank you Vedran. This did indeed help, immensely. Although I still got it to work with the LEFT JOINs as well, as long as I had the aliases right.
Thanks to everyone for their insight and assistance in this! As always, it is very much appreciated. So glad I have this forum and it's knowledgeable members to seek help from when I need it.
😀
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply