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

Question

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 ?

Answer 1

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