March 13, 2012 at 7:44 am
I can't seem to get this working right, the update statement say ambiguous.
IF OBJECT_ID('tempdb..##TempTm') > 0
DROP TABLE ##TempTm
CREATE TABLE ##TempTm
(AmtAuth varchar(50),
ReferenceNumber varchar(50),
CardNumber varchar(50),
Source varchar(50))
INSERT INTO ##TempTm
(AmtAuth, ReferenceNumber,
CardNumber, Source)
SELECT 0, 'A', '421134', 'S' UNION ALL
SELECT 100, 'A', '421134', 'A'
UPDATE ##TempTm
SET AmtAuth = B.AmtAuth
FROM ##TempTm A
JOIN ##TempTm B
ON A.ReferenceNumber = B.ReferenceNumber
AND A.CardNumber = B.CardNumber
March 13, 2012 at 7:49 am
UPDATE A
SET AmtAuth = B.AmtAuth
FROM ##TempTm A
JOIN ##TempTm B
ON A.ReferenceNumber = B.ReferenceNumber
AND A.CardNumber = B.CardNumber
March 13, 2012 at 7:54 am
Why global temp tables?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 13, 2012 at 7:55 am
Ahhhh ... thanks!
March 13, 2012 at 8:00 am
Guess I didn't indicate the result I needed. Need the 0 in AuthAmount when Source = S to be updated to 100. It does not update.
Why did I use global temps, no reason.
March 13, 2012 at 8:11 am
texpic (3/13/2012)
Guess I didn't indicate the result I needed. Need the 0 in AuthAmount when Source = S to be updated to 100. It does not update.Why did I use global temps, no reason.
update #TempTm
set AmtAuth = 100 where Source = 'S' and AmtAuth = 0
Unless there is a good reason for a global temp table you should avoid them. There are very few realistic uses for them. They can get you into all sort of problems if you are not careful. My advice would be not to use them and avoid the habit of using global temp tables.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 13, 2012 at 8:16 am
Actually this was just a small example. I have several transactions, that is why I need to use the self join.
March 13, 2012 at 8:21 am
texpic (3/13/2012)
Guess I didn't indicate the result I needed. Need the 0 in AuthAmount when Source = S to be updated to 100. It does not update.Why did I use global temps, no reason.
"When Source = S"? Which Source? In an updated table or in a joined one?
March 13, 2012 at 8:30 am
In that temp table there is a field called "source"
One record has the value "S," that is the record that needs to be updated (using a self join)
March 13, 2012 at 8:35 am
texpic (3/13/2012)
In that temp table there is a field called "source"One record has the value "S," that is the record that needs to be updated (using a self join)
Perhaps you need to put yourself in our shoes here. We get that you want to update the rows where source = "S" but where does the new value come from? Do you want to use the value the row with Source = "A" for the same reference number and cardnumber?
Did you actually look at the result set of your self join?
select a.*, b.*
FROM #TempTm A
JOIN #TempTm B
ON A.ReferenceNumber = B.ReferenceNumber
AND A.CardNumber = B.CardNumber
Given that which value(s) should be updated and from where? I don't think this is actually what you want. The self join you have right now is a cartesian.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 13, 2012 at 8:50 am
I'm sorry. Basically what I have is one transaction type that is a S, it does not have an amount. The A type transactions have the amount. So when Reference Number and Card Number match, update the S transaction with the A's amount value.
March 13, 2012 at 9:04 am
That is different story, if I understand you right, then:
UPDATE S
SET AmtAuth = A.AmtAuth
FROM ##TempTm S
JOIN ##TempTm A -- we join to the record of type A
ON A.ReferenceNumber = S.ReferenceNumber
AND A.CardNumber = S.CardNumber
AND A.Source = 'A'
WHERE S.Source = 'S' -- we update only records of type S
And, if you don't really need global temp table, use the local one - just with one #.
March 13, 2012 at 9:15 am
Yes that does it. Thanks, the notes help.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply