Geek Answers Handbook

SQL: updating a table from another table

I have two tables (with sample data):

tblZone

[ZoneNo] [Zone] -----1------Zone1 -----2------Zone2 -----3------Zone3

tblPointsClient

 [PolygonID] [Zone Name] -----0------------Zone1 -----0------------Zone1 -----0------------Zone1 -----0------------Zone2 -----0------------Zone2 -----0------------Zone2 -----0------------Zone3 -----0------------Zone3 -----0------------Zone3

Basically, using MySQL, I'm trying to update the PolygonID in tblPointsClient so that ZoneNo for the zone in tblZone (if that makes sense).

Therefore, when I run this update, it should change PolygonID to:

 [PolygonID] [Zone Name] -----1------------Zone1 -----1------------Zone1 -----1------------Zone1 -----2------------Zone2 -----2------------Zone2 -----2------------Zone2 -----3------------Zone3 -----3------------Zone3 -----3------------Zone3

I tried the following:

 UPDATE tblPointsClient SET tblPointsClient.PolygonID = ( SELECT ZoneNo FROM tblZones WHERE tblPointsClient.ZoneNo = tblZones.Zone )

but it did everything PolygonID 0

If anyone can help, that would be much appreciated! :)

+4
source share
2 answers
 UPDATE tblPointsClient a INNER JOIN tblZone b ON a.`Zone Name` = b.Zone SET a.PolygonID = b.ZoneNo

to improve performance, add INDEX to the Zone Name column in the tblPointsClient table and Zone in the tblZone table.

UPDATE 1

+3
source

You can use a subquery to achieve this:

 UPDATE tblPointsClient SET PolygonID = (SELECT TOP 1 ZoneNo FROM tblZone WHERE Zone = [Zone Name]) WHERE PolygonID = 0

I created code to update only rows using PolygonID = 0

+2
source

More articles:


All Articles