Update with Inner Join Statement

Other SQL Server Topics
1

Hi, I'm new to SQL Server/Database but have been using Access for many years.
I have a table which needs data on a single column updated.
I'm running the following statement:

UPDATE MasterTable
SET dbo.MasterTable.[X-Coord] = EnglandXYGrid.[X-Coord]
FROM dbo.MasterTable
INNER JOIN EnglandXYGrid
ON MasterTable.TrackID = EnglandXYGrid.[TrackID]
;
However, when I run the next statement, I would expect 0 rows, but it looks like the update has not worked:

SELECT MasterTable.trackid,MasterTable.[x-Coord] as MasterX, englandXYGrid.[x-Coord] as EnglandX
from MasterTable
inner join englandXYGrid
ON MasterTable.TrackID = englandXYGrid.TrackID
where MasterTable.[x-coord] <> englandXYGrid.[x-coord];

I'd appreciate some guidance.

2 
UPDATE MT
SET [X-Coord] = EXY.[X-Coord]
FROM dbo.MasterTable MT
INNER JOIN EnglandXYGrid EXY
ON MT.TrackID = EXY.[TrackID]
AND MT.[X-Coord] IS NULL OR MT.[X-Coord] <> EXY.[X-Coord]
;
3

Thanks for the quick response, however, I quit the query after it was running for over 15 mins.
There are a 8.1m records in MasterTable and 195k in EnglandXYGrid.
Should I expect it to run for so long? all the other queries I've run have taken no more than 4-5 mins.

4

The time would depend on the indexes on the tables and the total speed of the system.

You also should be able to break it into batches by doing the UPDATE on ranges of TrackID values rather than doing them all at once.

1 Like
5

Thanks for your help. this worked quickly for me.

UPDATE MT
SET [X-Coord] = EXY.[X-Coord]
FROM dbo.MasterTable MT
INNER JOIN EnglandXYGrid EXY
ON MT.TrackID = EXY.[TrackID]
AND MT.[X-Coord] <> EXY.[X-Coord]
AND MT.countryfk = '34'
;