0

I'm trying to use the following code to update my underlying table, FinancialsTest:

with AddressCTE as 
( 
    select person_id, address_line 
    from FinancialsTest
)
Update AddressCTE 
set address_line = y.address_line 
from AddressCTE x
join Demographics y on x.person_id = y.person_id

The result I get is the same address line repeated for every row, so it looks like the join isn't working.

Any suggestions?

Charlieface
  • 52,284
  • 6
  • 19
  • 43
sqlrobert
  • 13
  • 3
  • 1
    Your CTE is completely redundant here, you may as well join to the table – Stu May 25 '22 at 18:00
  • A CTE for an `UPDATE` can be a good idea to simplify the logic and allow you to test before updating, but then only if you actually put the `JOIN` in there too. That is, `WITH AddressCTE AS (SELECT ft.person_id, ft.address_line, d.address_line AS new_address_line FROM FinancialsTest ft JOIN Demographics d ON ft.person_id = d.person_id) UPDATE AddressCTE SET address_line = new_address_line`. – Jeroen Mostert May 25 '22 at 18:12
  • Relevant https://stackoverflow.com/a/68595587/14868997 and https://stackoverflow.com/a/72076254/14868997 – Charlieface May 25 '22 at 22:39

1 Answers1

1

The query proposed by jeroen mostert is better, it avoids you to use a CTE.

But if you need a CTE in another context (with more criteria in the CTE), you must specify the alias of the table to update, ie x:

with AddressCTE as 
( 
    select person_id, address_line 
    from FinancialsTest
)
update x
set address_line = y.address_line 
from AddressCTE x
join Demographics y on x.person_id = y.person_id
Rom Eh
  • 1,981
  • 1
  • 16
  • 33