Inserting data to another table based on some criteria

Question

I have two tables:

• Client: clientid, latt, long
• Region: Regionid, latt, long

I want transfer Regionid from Region to Client, where latt and long columns are matched.

Here is my code. I have no idea what to do:

INSERT INTO Client (Region)
SELECT Region.Regionid
FROM Region
INNER JOIN Client ON Client.Latt = Region.Latt
    AND Client.Long = Region.Long
WHERE Client.Latt = Region.Latt
    AND Client.Long = Region.Long

Should I create a Region column in Client table first?

Any ideas are appreciated!

Answer 1

Yes. You HAVE to create column Region in client first:

ALTER TABLE client ADD region int;

Change int to something else if regionId in table regio is of other type

Then you probably want to UPDATE instead of insert,

UPDATE c 
SET c.region = r.regionId
FROM client c
INNER JOIN region r ON c.latt = r.latt AND c.long = r.long

After the UPDATE you can drop the latt and long columns to have your tables normalized

sqlfiddle demo

Answer 2

You should indeed add another column to your Client table, let's just call it Region.

ALTER TABLE CLIENT
ADD REGION INTEGER

This will add the column to your table, then just do a UPDATE on that column like:

UPDATE C
SET Region = R.RegionID
FROM Region R
INNER JOIN CLIENT C ON C.LATT = R.LATT AND C.LONG = R.LONG

You don't need the conditions in the WHERE clause anymore, because they are implicit in the JOIN as they are the same conditions.