CODEWITHSUNDEEP
sql-server
Vian Ojeda Garcia
Vian Ojeda Garcia

Reputation: 867

SQL How to UPDATE Column via subquery with condition

Is it possible to update my a column in my table via subquery? the unique value is [ID] and [Date] How can I do this. So far I tried it like this but failed

UPDATE [dbo].[SCPL_EOD]
SET [CSP] = (SELECT 
  D.CSP FROM
(
  SELECT PDT.CallID,SCP.*
  ,ROW_NUMBER() OVER(PARTITION BY SCP.[MIN],[ACCOUNT_NUMBER] ORDER BY SCP.[DATE_TIME] DESC) as [Row]  FROM
  (
  SELECT *
  FROM [SCPL].[dbo].[SCPL_EOD]
  WHERE [SWITCH_RESULT] = 'ANSWERED'
  AND [Date] BETWEEN @DateFrom AND @DateTo
  ) SCP
  LEFT JOIN
  (
  SELECT 
  SUBSTRING([MIN],2,LEN([MIN])) as [MIN]
  ,CallID
  ,[Date]
  ,ROW_NUMBER() OVER(PARTITION BY [MIN],[PDcampaignBatch] ORDER BY PDTransaction.[SubmittedDateTime] DESC) as [Row] 
  FROM [Telemarketing].[dbo].[PDTransaction]
  WHERE [Date] BETWEEN @DateFrom AND @DateTo
  ) PDT ON SCP.[MIN] = PDT.MIN AND SCP.[Date] = PDT.[DATE] AND PDT.[Row] = 1
  )a 
 LEFT JOIN [Telemarketing].[dbo].[Disposition] d ON a.CallID = d.[Call ID]
  WHERE a.[Row] = 1
) a
WHERE [MIN] =

Upvotes: 1

Views: 52

Answers (1)

Keith
Keith

Reputation: 1038

The format you have is not correct for updating from a derived table, the format would be:

UPDATE  A
SET     A.Value     =   B.Value
FROM    Table_A         AS A
INNER JOIN 
(
    SELECT  B.ID        ,
            B.Value
    FROM    Table_B     AS B
)                       AS B ON B.ID = A.ID
WHERE   A.ID = 1

But I would suggest using temp tables if that is a possibility, as it makes readability much easier.

Upvotes: 1

Related Questions