SQL Query for Wards/Postcodes Taking Too LongI don't quite understand why this query
select distinct left(z.pc,4) from tblPostcodes as z where z.ward in (select distinct y.ward from tblPostcodes as y where y.pc like 'NW10%')has been running for over 48 hours and still is "Copying to tmp table", while the internal query
select distinct y.ward from tblPostcodes as y where y.pc like 'NW10%'returns 14 lines within 2--3 seconds, and subsequently the query which substitutes the internal query with its result
select distinct left(z.pc,4) from tblPostcodes as z where z.ward in ('E05000089', 'E05000100', 'E05000103', 'E05000102', 'E05000091', 'E05000104', 'E05000105', 'E05000087', 'E05000092', 'E05000098', 'E05000253', 'E05000175', 'E05000179', 'E05000085')again returns the result (12 lines) within 2 seconds.
The total number of lines in tblPostcodes is around 1.7 million, the number of rows matching "NW10%" is about 1800, and the number of rows within the 14 wards is approximately 4000.
Okay, I found out, something when I was trying to update a table (to copy a value from one row to another) in another project of mine:
The following query will not work as the SQL server complains that I cannot use the same table which is being updated in FROM clause:
UPDATE tblGeos SET Lat = (SELECT Lat FROM tblGeos WHERE StampID=57) WHERE StampID=58
But the following will work:
UPDATE tblGeos SET Lat = (SELECT Alat FROM (SELECT Lat AS Alat FROM tblGeos WHERE StampID=57) AS Z) WHERE StampID=58