UPDATE Query in Access updating Zero rows

[AccessHelp123]

Hi,

I wrote a query to Update a field in table1 with a particular field in table2 based on three matching criteria. The problem is that it works for similar smaller tables but wont work for larger table with about 80000 rows.
The query is below.

UPDATE Data INNER JOIN tblSiteNames ON (Data.BSC = tblSiteNames.bscId) AND (Data.BTS_Site = tblSiteNames.btsSMId) AND (Data.Sector = tblSiteNames.btsId) SET Data.SiteName = [tblSiteNames].[short_name]
WHERE (((Data.BSC)=[tblSiteNames].[bscId]) AND ((Data.BTS_Site)=[tblSiteNames].[btsSMId]) AND ((Data.Sector)=[tblSiteNames].[btsId]));

Please help. Thanks.

 

[PHV]

Your WHERE clause is redundant with the JOIN.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[AccessHelp123]

Thanks PHV. I tried the query after deleting the WHERE clause. Still, it works for small tables and not for large ones. Is there anything different I need to do with larger tables? I have been trying different things to make this work, nothing seems to fix it.

 

[Golom]

First thing to check is whether or not there are in fact records to update

Select * 

From Data As D INNER JOIN tblSiteNames As S
       ON  D.BSC      = S.bscId 
       AND D.BTS_Site = S.btsSMId 
       AND D.Sector   = S.btsId

If that doesn't return any records then the UPDATE is working properly. If it does return records then it looks like your WHERE clause is redundant since it just repeats the conditions in the ON clause of the join. Try this

UPDATE Data As D INNER JOIN tblSiteNames As S
       ON  D.BSC      = S.bscId 
       AND D.BTS_Site = S.btsSMId 
       AND D.Sector   = S.btsId 

SET D.SiteName = S.short_name

Another thing to check is the possibility that "SiteName" is a key field and by resetting it you are creating duplicates that are not allowed.

 

[AccessHelp123]

Thanks everyone. Finally figured it out. It was a minute thing that I missed. For some reason the records started of with a few 'spaces' and so the update query was not able to find it.