UPDATE table via join in SQL

Question

I am trying to normalize my tables to make the db more efficient.

To do this I have removed several columns from a table that I was updating several columns on.

Here is the original query when all the columns were in the table:

UPDATE myActDataBaselDataTable 
set [Correct Arrears 2]=(case when [Maturity Date]='' then 0 else datediff(d,convert(datetime,@DataDate, 102),convert(datetime,[Maturity Date],102)) end)
from myActDataBaselDataTable

Now I have removed [Maturity Date] from the table myActDataBaselDataTable and it's necessary to retrieve that column from the base reference table ACTData, where it is called Mat.

In my table myActDataBaselDataTable the Account number field is a concatenation of 3 fields in ACTData, thus

myActDataBaselDataTable.[Account No]=ac.[Unit] + ' ' + ac.[ACNo] + ' ' + ac.[Suffix]

(where ac is the alias for ACTData)

So, having looked at the answers given elsewhere on SO (such as 1604091: update-a-table-using-join-in-sql-server), I tried to modify this particular update statement as below, but I cannot get it right:

UPDATE myActDataBaselDataTable 
set dt.[Correct Arrears 2]=(
    case when ac.[Mat]='' 
        then 0 
        else datediff(d,convert(datetime,'2014-04-30', 102),convert(datetime,ac.[Mat],102))
    end)
from ACTData ac 
inner join myActDataBaselDataTable  dt
    ON dt.[Account No]=ac.[Unit] + ' ' + ac.[ACNo] + ' ' + ac.[Suffix]

I either get an Incorrect syntax near 'From' error, or The multi-part identifier "dt.Correct Arrears 2" could not be bound.

I'd be grateful for any guidance on how to get this right, or suugestiopns about how to do it better.

thanks

EDIT:

BTW, when I run the below as a SELECT it returns data with no errors:

select case when [ac].[Mat]='' 
            then 0 
                else datediff(d,convert(datetime,'2014-04-30', 102),convert(datetime,[ac].[Mat],102))
        end
        from ACTData ac 
    inner join myActDataBaselDataTable  dt
        ON dt.[Account No]=ac.[Unit] + ' ' + ac.[ACNo] + ' ' + ac.[Suffix]

thanks for that (very simple, but eluded me totally!), perhaps you could expand on it into answer that I could choose to help the next person who finds it? — Our Man in Bananas, Jun 03 '14 at 12:32

paparazzo · Accepted Answer · 2014-06-03T12:54:11.547

0

In a join update, update the alias

update dt

What is confusing is that in later versions of SQL you don't need to use the alias in the update line

edited Jun 03 '14 at 12:54

answered Jun 03 '14 at 12:47

paparazzo

44,497
23
105
176

UPDATE table via join in SQL

1 Answers1