March 3, 2008 at 4:21 pm
Howdy. I have a customer table rm00101 that has a field userdef2 that needs to be updated with the data in a response code field from a credit card transaction table ms273512. The thing is, the rm00101's PK is the customer number(of course), but the credit card table's PK is a transaction number, but it tracks the customer number. The credit card table tracks every credit card either approved or declined.
My goal is to update the userdef2 customer field with the response code for the most recent declined credit card transaction. Is there any way to write an update query doing such a thing? Any help would be awesome. Thank you.
March 3, 2008 at 5:43 pm
create trigger
on ms273512
after insert, update
begin
if ( update( {approval_field} ) )
begin
update rm00101
set userdef2 = {some_value}
from rm00101 join inserted
on rm00101.{customer_id} = inserted.{customer_id}
where inserted.{approval_field} = {declined}
end
March 4, 2008 at 6:12 am
wtipton (3/3/2008)
Howdy. I have a customer table rm00101 that has a field userdef2 that needs to be updated with the data in a response code field from a credit card transaction table ms273512. The thing is, the rm00101's PK is the customer number(of course), but the credit card table's PK is a transaction number, but it tracks the customer number. The credit card table tracks every credit card either approved or declined.My goal is to update the userdef2 customer field with the response code for the most recent declined credit card transaction. Is there any way to write an update query doing such a thing? Any help would be awesome. Thank you.
BTW, very clear table names... :sick:
Anyway
UPDATE rm00101
SET userdef2 = m.ResponceCode
FROM ms273512 m
WHERE m.CustomerNumber = rm00101.CustomerNumber
AND m.TransactionDate = (SELECT TOP(1) m2.TransactionDate
FROM ms273512 m2
WHERE m2.CustomerNumber = m.CustomerNumber
ORDER BY m2.TransactionDate DESC)
That could probably be cleaned up a bit to make it a little more set based. It's just off the top of my head.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 4, 2008 at 8:36 am
Thank you for your assistance, it means a lot! I will go ahead and attempt this. Something I did leave out it that I will have to do a CASE statement on the response code due to the smaller field size in userdef2. That should work too, right? Again, I apolozige for these questions if they seem too newbie-ous. I just really want to learn SQL super well enough to get certified. Thanks again.
March 4, 2008 at 9:22 am
Yeah, you should be able to employee a CASE statement, but it depends on where you mean to employee it.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 4, 2008 at 9:57 am
It all works! I've tested the update query successfully with employing a CASE statement like this:
UPDATE rm00101
SET userdef2 = (CASE cc.mso_respcode
WHEN -12 THEN rm00101.userdef2
WHEN -31 THEN rm00101.userdef2
WHEN 0 THEN rm00101.userdef2
WHEN 4 THEN rm00101.userdef2
WHEN 12 THEN 'CC Declined'
WHEN 19 THEN rm00101.userdef2
WHEN 13 THEN 'Call Auth Center'
WHEN 23 THEN 'Invalid CC #'
WHEN 24 THEN 'Invalid EXP Date'
WHEN 99 THEN 'CC Declined'
WHEN 108 THEN rm00101.userdef2
WHEN 117 THEN rm00101.userdef2
ELSE 'Change Query Dork'
END)
FROM ms273512 AS cc
WHERE cc.custnmbr = rm00101.custnmbr
AND cc.mso_last_xmit_date = (SELECT TOP(1) cc2.mso_last_xmit_date
FROM ms273512 AS cc2
WHERE cc2.custnmbr = cc.custnmbr
ORDER BY cc2.mso_last_xmit_date DESC)
THANK YOU!
March 4, 2008 at 10:28 am
Just another way of doing it... puts all the different "answers" to the case in groups...
UPDATE rm00101
SET userdef2 = (CASE
WHEN cc.mso_respcode IN (-12,-31,0,4,19,108,117) THEN rm00101.userdef2
WHEN cc.mso_respcode IN (12,99) THEN 'CC Declined'
WHEN cc.mso_respcode = 13 THEN 'Call Auth Center'
WHEN cc.mso_respcode = 23 THEN 'Invalid CC #'
WHEN cc.mso_respcode = 24 THEN 'Invalid EXP Date'
ELSE 'Change Query Dork'
END)
FROM ms273512 AS cc
WHERE cc.custnmbr = rm00101.custnmbr
AND cc.mso_last_xmit_date = (SELECT TOP(1) cc2.mso_last_xmit_date
FROM ms273512 AS cc2
WHERE cc2.custnmbr = cc.custnmbr
ORDER BY cc2.mso_last_xmit_date DESC)
--Jeff Moden
Change is inevitable... Change for the better is not.
March 4, 2008 at 10:31 am
Gotta say - that's a catchy "else" clause.....:D
Might have to reuse that somewhere.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
March 4, 2008 at 10:37 am
Is that ELSE clause a documented standard? I clearly need to update my documents.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 4, 2008 at 10:39 am
Grant Fritchey (3/4/2008)
Is that ELSE clause a documented standard? I clearly need to update my documents.
I think it would make Sergyi proud....:):hehe:
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
March 4, 2008 at 10:50 am
Haha. Just in case something new comes up. I have a select statement that will pick up any conditions where the ELSE would be met. Haha. Thanks again.
Viewing 11 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply