Issue with the update query

Database
Enthusiast

Issue with the update query

I have been trying to write an update statement to update null party id's from another table.

Not sure what's the error is. Below is the code:

update dp.contact_event_p CE from
(
select distinct A.dscusno,A.DSDMNO,c.Party_id from DP_EDW.LL_DSDETL A
inner join dp_vew.contact_event B
on A.DSDMNO=B.contact_src_num and B.Party_id is null and B.EVENT_TYPE_CD='ILL'
inner join dp_vew.party_identification C
on A.dscusno= c.party_identification_num ) DER
set party_id =DER.PARTY_ID
where CE.CONATCT_SRC_NUM=DER.DSDMNO
and CE.Party_id is null and CE.EVENT_TYPE_CD='ILL'

can anyone tell me what am i missing here?Is it the alias issue?

Thanks,
Amit

 


Accepted Solutions
Junior Contributor

Re: Issue with the update query

The UPDATE FROM syntax is a bit awkward, this should work:

UPDATE CE FROM
dp.contact_event_p CE ,
(
SELECT DISTINCT A.dscusno,A.DSDMNO,c.Party_id FROM DP_EDW.LL_DSDETL A
INNER JOIN dp_vew.contact_event B
ON A.DSDMNO=B.contact_src_num AND B.Party_id IS NULL AND B.EVENT_TYPE_CD='ILL'
INNER JOIN dp_vew.party_identification C
ON A.dscusno= c.party_identification_num ) DER
SET party_id =DER.PARTY_ID
WHERE CE.CONATCT_SRC_NUM=DER.DSDMNO
AND CE.Party_id IS NULL AND CE.EVENT_TYPE_CD='ILL'

 

1 ACCEPTED SOLUTION
3 REPLIES
Junior Contributor

Re: Issue with the update query

The UPDATE FROM syntax is a bit awkward, this should work:

UPDATE CE FROM
dp.contact_event_p CE ,
(
SELECT DISTINCT A.dscusno,A.DSDMNO,c.Party_id FROM DP_EDW.LL_DSDETL A
INNER JOIN dp_vew.contact_event B
ON A.DSDMNO=B.contact_src_num AND B.Party_id IS NULL AND B.EVENT_TYPE_CD='ILL'
INNER JOIN dp_vew.party_identification C
ON A.dscusno= c.party_identification_num ) DER
SET party_id =DER.PARTY_ID
WHERE CE.CONATCT_SRC_NUM=DER.DSDMNO
AND CE.Party_id IS NULL AND CE.EVENT_TYPE_CD='ILL'

 

Enthusiast

Re: Issue with the update query

Thanks a lot Dnoeth!! Is this issue with TD only?

Tags (1)
Junior Contributor

Re: Issue with the update query

As UPDATE FROM is no Standard SQL syntax different vendors may have different rules (e.g. afaik SQL Server supports Outer Joins)