CODEWITHSUNDEEP
mysqlsqlsql-update
nsilva
nsilva

Reputation: 5612

SQL: Update 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 am trying to update the PolygonID in tblPointsClient by what the ZoneNo is for the Zone in tblZone (if that makes sense).

So when I run this update it should change the PolygonID's to:

[PolygonID] [Zone Name]

-----1------------Zone1
-----1------------Zone1
-----1------------Zone1
-----2------------Zone2
-----2------------Zone2
-----2------------Zone2
-----3------------Zone3
-----3------------Zone3
-----3------------Zone3

I have tried the following:

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

but this made all the PolygonID's 0

If anybody could help, that would be much appreciated! :)

Upvotes: 4

Views: 119

Answers (2)

veljasije
veljasije

Reputation: 7092

You can use subquery to achieve this goal:

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

I have created code for updating only rows with PolygonID = 0

Upvotes: 2

John Woo
John Woo

Reputation: 263723

UPDATE  tblPointsClient a
        INNER JOIN tblZone b
            ON a.`Zone Name` = b.Zone
SET     a.PolygonID = b.ZoneNo

for faster performance, add an INDEX on column Zone Name on table tblPointsClient and Zone on table tblZone.

UPDATE 1

Upvotes: 3

Related Questions