Copying columns between tables, fills entire column with the same value-CodePudding

Im trying to copy data from one table to another. They both have integer columns, AIRPORTS_DATA.ADDRESS_ID and AIRPORT_ADDRESS.AIRPORT_ID. When I do something like

UPDATE AIRPORTS_DATA AS AD
SET ADDRESS_ID = AA.AIRPORT_ID
FROM AIRPORTS_DATA
INNER JOIN AIRPORT_ADDRESS AS AA
ON AIRPORTS_DATA.ADDRESS_ID = AA.AIRPORT_ID
WHERE AA.AIRPORT_ID > 0

(not sure about WHERE, just trying to copy entire column) While it does fill the address_id column, it fills with first value from airport_id column (in my case 2). So I get a column of 2s. What am I doing wrong?

Edit1: Bunch of test data: First table is airports_data, second one is airport_address

airport_code	airport_name	address_id
AAQ	Anapa	[null]
ABA	Abakan	[null]

airport_id	airport_region	airport_city
2	def	abc
5	fre	gere

Second table is random generated

CodePudding user response：

Rethinking on what Adrian Klaver said, maybe now I understand where you were going wrong (the query itself is wrong as a whole but let's assume it was something more meaningful).

UPDATE AIRPORTS_DATA AS AD
SET airport_city = AA.airport_city
FROM AIRPORTS_DATA
INNER JOIN AIRPORT_ADDRESS AS AA
ON AIRPORTS_DATA.ADDRESS_ID = AA.AIRPORT_ID
WHERE AA.AIRPORT_ID > 0;

If there were at least one matching iten, then the first matching item's information would be used in updating the table. For sampling I added airport_city = AA.airport_city update. It was nonsense trying to update something that you are using for setting the relation anyway.

The correct way of doing that would be:

UPDATE AIRPORTS_DATA
SET airport_city = AA.AIRPORT_CITY
FROM AIRPORT_ADDRESS AS AA
WHERE AIRPORTS_DATA.ADDRESS_ID = AA.AIRPORT_ID 
  and AA.AIRPORT_ID > 0;

Here is a DBFiddle demo showing the issue and correct way of doing that.