What is wrong with this query????? 3

[arif101]

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)

 

[bborissov]

UPDATE Prospect
SET LastContactDate = TempBill.Date1
FROM Prospect
INNER JOIN (SELECT Number,
                   MAX(TempBill.[Date]) AS Date1
                   FROM  TempBill
                   GROUP BY Number) TempBill
 ON Prospect.Phonenumber = TempBill.Number AND
    Prospect.LastContactDate < TempBill.Date1

not tested

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[arif101]

Hi bborissov,

cheers for that code, I have just given it a quick go but it seems to be locking the database, even when I put in the with (nolock) option. Is there anyway that I could do this without locking the database or am I best just waiting till the systems are offline?

There will be about 500,000 records in the tempbill table and there are over 200,000,000 records in the prospect table so i can understand there will be some load but if it could be done without freezing the systems that would be better.

Again thanks for your solution.

 

[bborissov]

Do this as a scheduled task that will run sometime after midnight (or when th system is free).

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[arif101]

Cheers I will give it a go tonight and let you know the outcome.

Thanks again for your help!!

 

[woogoo]

Hi,

You may also want to make sure that the Number and TempBill.Date fields are indexed as well as this will boost the processing speed greatly.

HTH

 

[MichaelF81]

I was just reading and thinking the same thing.




"Adults are just obsolete children and the hell with them." - Dr. Seuss

 

[arif101]

Sorry only just realised I didn't reply to this thread....

Thanks for the help it is working a treat!!!!

Arif