I am trying to update a table in our database from information in another table but i need the newer date to stay in the updated table. I have used the following script but it doesn't seem to work please could someone tell me where it is wrong.
UPDATE Prospect
SET LastContactDate = (SELECT TempBill.[Date] FROM TempBill WITH (NOLOCK)
WHERE TempBill.Number = Prospect.Phonenumber)
WHERE EXISTS (SELECT TempBill.Number FROM TempBill WITH (NOLOCK)
WHERE TempBill.Number = Prospect.PhoneNumber)
AND LastContactDate < (SELECT TempBill.[Date] FROM TempBill WITH (NOLOCK)
WHERE TempBill.Number = Prospect.PhoneNumber)
UPDATE Prospect
SET LastContactDate = (SELECT TempBill.[Date] FROM TempBill WITH (NOLOCK)
WHERE TempBill.Number = Prospect.Phonenumber)
WHERE EXISTS (SELECT TempBill.Number FROM TempBill WITH (NOLOCK)
WHERE TempBill.Number = Prospect.PhoneNumber)
AND LastContactDate < (SELECT TempBill.[Date] FROM TempBill WITH (NOLOCK)
WHERE TempBill.Number = Prospect.PhoneNumber)