SQL Update based on multiple select statements using inner join-CodePudding

I am trying to update multiple columns in a table based on an inner join from another two tables (where these two tables match based on a column). The logic is to update comment1 and comment2 in the bos_output table if the pnl column matches with the code column.

UPDATE [dbo].[bos_output]
SET comment1 = "Invalid/Incomplete Record"
SET comment2 = "Invalid P&L for Local LOB"

SELECT code  FROM 
(SELECT distinct pnl FROM [dbo].[bos_output] where lob = 'LOB0002' GROUP BY pnl) t1 
INNER JOIN 
(SELECT code FROM [dbo].[bos_dim_code] where code_status = 'Valid Code - EA new sector' GROUP BY code) t2
ON t1.pnl = t2.code

The question is, could this be achieved using this statement? I am intending to store this in SSIS.

CodePudding user response：

I'm not sure why you're even grouping or using sub-queries, it would appear you could use an exists correlation:

update b set 
  comment1 = 'Invalid/Incomplete Record',
  comment2 = 'Invalid P&L for Local LOB'
from dbo.bos_output b
where exists (
  select * from dbo.bid_dim_code c
    where c.code_status = 'Valid Code - EA new sector'
    and b.pnl = c.code
);

Note that an update uses a single set keyword and single quotes delimit strings; [identifiers] are only necessary for reserved words or certain characters and (imho) make queries harder to read.

CodePudding user response：

Try something like:

UPDATE [dbo].[bos_output]
SET 
    comment1 = 'Invalid/Incomplete Record',
    comment2 = 'Invalid P&L for Local LOB'
WHERE
    pnl IN
(
SELECT pnl FROM 
(SELECT pnl FROM [dbo].[bos_output] where lob = 'LOB0002' GROUP BY pnl) t1 
INNER JOIN 
(SELECT code FROM [dbo].[bos_dim_code] where code_status = 'Valid Code - EA new sector' GROUP BY code) t2
ON t1.pnl = t2.code
)

You don't need the DISTINCT if you are using GROUP BY.

UPDATE The inner selects could be tidied

UPDATE [dbo].[bos_output]
SET 
    comment1 = 'Invalid/Incomplete Record',
    comment2 = 'Invalid P&L for Local LOB'
WHERE
    pnl IN
(
SELECT DISTINCT pnl 
FROM 
    [dbo].[bos_output] AS t1 
INNER JOIN 
    [dbo].[bos_dim_code] AS t2) t2
ON t1.pnl = t2.code
WHERE
    t1.lob = 'LOB0002'
    AND
    t2.code_status = 'Valid Code - EA new sector'
)