Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

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

Status
Not open for further replies.

arif101

IS-IT--Management
Jan 26, 2007
5
GB
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)
 
Code:
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
 
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.
:)
 
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
 
Cheers I will give it a go tonight and let you know the outcome.

Thanks again for your help!!

happy.gif
 
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
 
I was just reading and thinking the same thing.




"Adults are just obsolete children and the hell with them." - Dr. Seuss
 
Sorry only just realised I didn't reply to this thread....

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

Arif
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top