question

ahmedsalah-1628 avatar image
0 Votes"
ahmedsalah-1628 asked Viorel-1 answered

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

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-generalsql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

Viorel-1 avatar image
0 Votes"
Viorel-1 answered

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 

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.