how to make these two statment on one statment for update?

ahmed salah 3,216 Reputation points
2021-09-18T14:20:06.243+00:00

I work on sql server 2012 i need to make update statement on one statment
instead of using two statment

select p.chemicalid,count(p.chemicalid) as CountChemicals
            into #countchemicalprofiles
            from parts.chemicalprofiles p with(nolock)
            inner join  #TempPC t on t.chemicalid=p.chemicalid
            group by  p.chemicalid

              UPDATE  t
            SET     t.[status] = 'Count chemical on profile not equal Master'
            FROM    #TempPC t
                    INNER JOIN #countchemicalprofiles cm ON cm.chemicalid = t.chemicalid
                    AND cast(cm.CountChemicals as int)<>t.RowsCount                                    
            WHERE   t.[status] IS NULL   

so how to do that please ?

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,117 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,583 questions
0 comments No comments
{count} votes

Accepted answer
  1. Viorel 114K Reputation points
    2021-09-18T16:45:25.603+00:00

    Try something like this:

    ;
    with countchemicalprofiles as
    (
       select p.chemicalid,count(p.chemicalid) as CountChemicals
       from parts.chemicalprofiles p with(nolock)
       inner join #TempPC t on t.chemicalid=p.chemicalid
       group by  p.chemicalid
     )               
    UPDATE  t
    SET     t.[status] = 'Count chemical on profile not equal Master'
    FROM    #TempPC t
    INNER JOIN countchemicalprofiles cm ON cm.chemicalid = t.chemicalid
                         AND cast(cm.CountChemicals as int)<>t.RowsCount                                    
    WHERE   t.[status] IS NULL 
    
    0 comments No comments

0 additional answers

Sort by: Most helpful