Efficient batch update/synchronization

[Guest_imported]

Hi all, I'm a newbie, and I was wondering if there's a more efficient way to accomplish the update the SQL query below performs:

UPDATE mls
SET bath_level =
(SELECT bath_level
FROM idxmls1
WHERE id = [mls].[id]), class = 1, list_price =
(SELECT list_price
FROM idxmls1
WHERE id = [mls].[id])
WHERE (lister_office_name NOT LIKE ' % spencer % ') AND (lister_office_name NOT LIKE ' % hughes % ') AND (town IN
(SELECT town
FROM areas))

 

[tlbroadbent]

Use a JOIN query. The following will be much more efficient.

UPDATE mls SET
bath_level = i.bath_level,
class = 1,
list_price = i.list_price
FROM mls m
JOIN idxmls1 i
m.id = i.id
WHERE (lister_office_name NOT LIKE ' % spencer % ')
AND (lister_office_name NOT LIKE ' % hughes % ')
--The code doesn't indicate which table
--has the columntown so I assumed mls
AND Exists (SELECT * FROM areas Where town=m.town) Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.