How to update 2 field in 2 different condition ?

See more:

SQL-Server-2005

SQL-Server

Hi Expert,

I am uday Satardekar,

I am inserting data(emails) in sql server 2005 using stored procedure.
I have 2 scenario-

1.
IF i found any duplicate email then i update only status in certain direction.means if status of duplicate email is lesser than database email status then i allow to update.

date and status information stored in companyinfo table.and email is stored in emailinfo table.

#TempTable in stored procedure is temporary table which contains fresh and duplicate emails.

For updating status of duplicates emails according to situation i wrote following code.

UPDATE dbo.companyinfo 

SET companyinfo.status=RESULT.status

FROM

(	
select TEMP1.status,TEMP1.DataID
from

		(	
			SELECT tmp.email,tmp.status,Email.DataID,ROW_NUMBER() OVER (PARTITION BY tmp.email ORDER BY tmp.email ) AS 'RowNumber'
			FROM #TempTable tmp 
			LEFT OUTER JOIN emailinfo Email ON tmp.email=Email.email			
										
			WHERE 
			  tmp.email !=''		
			AND 
			EXISTS (SELECT  * FROM dbo.emailinfo WHERE email=tmp.email)
		)AS TEMP1

	LEFT OUTER JOIN dbo.companyinfo COMPANY ON TEMP1.DataID=COMPANY.dataId	

	where --cmp.status !=8	And
	TEMP1.RowNumber=1       AND
	COMPANY.status !=1			AND 
	COMPANY.status < 6         AND
	TEMP1.status < COMPANY.status

) AS RESULT

WHERE companyinfo.dataId=RESULT.DataID

2.
Now how to update date in all condition? Means if i found any duplicates email then it update date also.

How to achieve both condition - 1. update status with condition ,and 2. update date in for all duplicates with efficient manner ?


Thanks in advance.