Joins in Where clause of UPDATE function

Transact-SQL
1

UPDATE [dbo].[ChargeInsuranceDetails]
SET [process_claim] = 'N'
WHERE [charge_id] = (SELECT charge_id FROM [dbo].[ChargeInsuranceDetails] inner join [dbo].[charges] ON
[dbo].[ChargeInsuranceDetails].[charge_id]=[dbo].[charges].[id]
WHERE [dbo].[ChargeInsuranceDetails].process_claim='Y' and [dbo].[charges].accounting_transaction_date < '01/01/2020')
GO

This is what I am trying to accomplish but apparently INNER JOINS are not permitted in this context. Can I get some assistance as to the proper syntax to complete this? I need to update 7300+ records.

2

Update cd
SET [process_claim] = 'N'
FROM [dbo].[ChargeInsuranceDetails] cd
inner join [dbo].[charges] d
ON cd.[charge_id]= c.[id]
WHERE cd.process_claim='Y'
and c.accounting_transaction_date < '01/01/2020'

3 Likes
3

First try this in test or first try the select statement before implementing. Notice the use of aliases for your tables. Also removed all the brackets. Clean as a whistle


--try this first
select *
FROM dbo.ChargeInsuranceDetails  tgt
inner join dbo.charges c 
   ON tgt.charge_id =c .id
WHERE tgt.process_claim='Y' 
  and c.accounting_transaction_date < '01/01/2020'

--then when you are sure you want to implement it try the following
UPDATE tgt
   SET tgt.process_claim = 'N'
 FROM dbo.ChargeInsuranceDetails  tgt
inner join dbo.charges c 
   ON tgt.charge_id =c .id
WHERE tgt.process_claim='Y' 
  and c.accounting_transaction_date < '01/01/2020'
2 Likes
4

@mike01 sorry did not even see your answer or you posted same exact time

1 Like