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
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.