How do I update MSSQL table values from the contents of a second table using the ID from a third table to match the records?

Question

I need to update table CSPM.TRACT with the values of CFPL.SVALUE where CSPM.ID = PM.PMPERMITID AND CSPM.TRACT IS NULL.

Here are the joins in place between the three tables: PM LEFT OUTER JOIN CFPL INNER JOIN CSPM ON CFPL.GCUSTOMFIELDPICKLISTITEM = CSPM.TRACTS ON PM.PMPERMITID = CSPM.ID

Can you provide more information, it looks like you may be leaving things out. — Azeame
– Azeame, Commented Dec 31, 2019 at 20:02

Bharati Mathapati · Accepted Answer · 2020-01-01 11:49:44Z

2

Try this -

 UPDATE CSPM SET CSPM.TRACT = COALESCE (CSPM.TRACT, CFPL.SVALUE) FROM CSPM JOIN PM ON CSPM.ID = PM.PMPERMITID LEFT OUTER JOIN CFPL ON CFPL.GCUSTOMFIELDPICKLISTITEM = CSPM.TRACTS

answered Jan 1, 2020 at 11:49

Bharati Mathapati

1193 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user3018012 Over a year ago

Sorry Bharati, I'm new to this board and didn't know to do that. Thanks again!

Ronk · Accepted Answer · 2019-12-31 20:24:32Z

When dealing with outer joins sometimes the where clause restricts the normal outer join. Fortunately with a coalesce you can get around this complication and have a relatively straight-forward query:

update CSPM set TRACT = coalesce(CSPM.TRACT, CFPL.SVALUE) from CSPM join PM on CSPM.ID = PM.PMPERMITD left join CFPL on CFPL.GCUSTOMFIELDPICKLISTITEM = CSPM.TRACTS

Collectives™ on Stack Overflow

How do I update MSSQL table values from the contents of a second table using the ID from a third table to match the records?

2 Answers 2

1 Comment

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Related