Inner join question 1

[djj55]

Hello, SQL2008R2
I have an inner join that joins a one row table to a 10 row table. When I do a select it returns all ten rows:

SELECT * 
FROM CAR -- One row
INNER JOIN CHR -- Ten rows
ON CAR.CallIDX = CHR.CallHistoryIDX

However, I then try to do an update which only does one update:

UPDATE CAR 
SET Condolences =   CASE WHEN CHR.AuditCode = '00100' THEN LEFT(CHR.Response, 50) ELSE CAR.Condolences END 
    ,IDSelf =       CASE WHEN CHR.AuditCode = '10000' THEN LEFT(CHR.Response, 50) ELSE CAR.IDSelf END 
    ,ConfirmParty = CASE WHEN CHR.AuditCode = '10010' THEN LEFT(CHR.Response, 50) ELSE CAR.ConfirmParty END 
FROM CAR 
INNER JOIN CHR ON CAR.CallIDX = CHR.CallHistoryIDX

CHR has all three AuditCode values (three records) for the value CAR.CallIDX, but it only updates one column.

I must be missing something but I do not know what. Why does the UPDATE not change three columns for the one record in CAR?
Thank you,

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[imex]

Hi,

Try something like this:

UPDATE CAR 
SET Condolences =   COALESCE(LEFT(R.Response, 50), CAR.Condolences)
    ,IDSelf =       COALESCE(LEFT(I.Response, 50), CAR.IDSelf) 
    ,ConfirmParty = COALESCE(LEFT(P.Response, 50), CAR.ConfirmParty)
FROM CAR 
LEFT JOIN CHR AS C 
    ON CAR.CallIDX = C.CallHistoryIDX AND
       C.AuditCode = '00100'
LEFT JOIN CHR AS I 
    ON CAR.CallIDX = I.CallHistoryIDX AND
       I.AuditCode = '10000'
LEFT JOIN CHR AS P
    ON CAR.CallIDX = P.CallHistoryIDX AND
       P.AuditCode = '10010'

Hope this help.

[URL unfurl="true"]http://www.imoveisemexposicao.com.br/imobiliarias-em-guarulhos[/url]

 

[djj55]

Thank you for your reply.

Unfortunitly this is a small portion of a dynamic SQL query. The columns can change as this is building a report. The actual tables involved have several thousand records.

My question is more why the update does not update the table with one record based on all ten of the records in the second table?

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[gmmastros]

Try this:

;With Data As
(
    Select  Car.CallIdx,
            Min(CASE WHEN CHR.AuditCode = '00100' THEN LEFT(CHR.Response, 50) END) As Condolences, 
            Min(CASE WHEN CHR.AuditCode = '10000' THEN LEFT(CHR.Response, 50) END) As IDSelf, 
            Min(CASE WHEN CHR.AuditCode = '10010' THEN LEFT(CHR.Response, 50) END) As ConfirmParty 
    FROM    CAR 
            INNER JOIN CHR ON CAR.CallIDX = CHR.CallHistoryIDX 
    Group By Car.CallIdx
)
Update  CAR
Set     CAR.Condolences = Data.Condolences,
        CAR.IDSelf = Data.IDSelf,
        CAR.ConfirmParty = Data.ConfirmParty
From    CAR
        Inner Join Data
          On CAR.CallIdx = Data.CallIdx

The reason your original query did not work is sorta complex. Your first query returns 10 rows. When SQL Server makes the assignment (actually the update statement), it is essentially assigning values 10 times over. So while it's true that the data is updated for 1 column, you need to consider what happens during the other 9 updates for each row.

The query I show above is one method of pivoting the data. If you run the code in the CTE block, you'll see that there is just one row for each CallIdx, so that when the assignment is made, there is no ambiguity regarding which row to update from because there is only 1.

I hope this makes sense because I'm not sure I completely understand it either.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[djj55]

Thanks George,
I will use your information to try and understand.

In the mean time, I ended up using a loop with a row_number counter to do what I needed.

All this just to get ride of a cursor.

Thanks to all,

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[gmmastros]

Well... the query I show above doesn't use a cursor or a loop and will probably perform really well.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[djj55]

Hello George,
As it turns out, what I need can be done with just the cte portion of your example. Since the table I was updating gets populate with the unique CallIDX in the step before, the aggregate works great. This cuts a couple of steps out of my code.

Thanks again,

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!