How can I “Update Select” some values in a column with a subquery?

I am using Apache Derby DB (SQL) version 10.14 and this is the Derby Reference Manual: https://db.apache.org/derby/docs/10.14/ref/refderby.pdf

I am trying to create a star schema and am currently working on the region dimension table.

These are the tables I am using: DWH_PRICE_PAID_RECORDS

TRANSACTION_ID                          PRICE   DATE_OF_TRANSFER    PROPERTY_TYPE   OLD_NEW     DURATION    TOWN_CITY               DISTRICT                COUNTY                  PPDCATEGORY_TYPE    RECORD_TYPE
{FDD12C8B-5A02-4B3F-8C67-9BC523DC780B}  71000   15.09.2000 00:00    D                   N       F           SCUNTHORPE              NORTH LINCOLNSHIRE      NORTH LINCOLNSHIRE      A                       A
{70F7F480-4A9A-4FEB-A58A-2B964605BFD2}  97000   01.08.2002 00:00    F                   N       L           MANCHESTER              SALFORD                 GREATER MANCHESTER      A                       A
{64D48FA9-8C85-49D6-AF5A-23FABDDB4FEB}  104000  17.01.2006 00:00    S                   N       L           ROCHDALE                ROCHDALE                GREATER MANCHESTER      A                       A
{F0316F65-E375-4DC4-BCDF-3FDC054ADE9C}  188500  18.05.2015 00:00    S                   N       F           KIDDERMINSTER           MALVERN HILLS           WORCESTERSHIRE          A                       A
{2EC5A85B-7BEF-4127-B3D0-6B416899CAEB}  180000  07.05.1999 00:00    S                   N       F           KINGSTON UPON THAMES    KINGSTON UPON THAMES    GREATER LONDON          A                       A
{21E5FEB7-A62E-2439-E050-A8C06205342E}  55000   28.08.2015 00:00    T                   N       F           MOUNTAIN ASH            RHONDDA CYNON TAFF      RHONDDA CYNON TAFF      B                       A
{3E0330F0-0F44-8D89-E050-A8C062052140}  77000   30.08.2016 00:00    T                   N       F           WALLASEY                WIRRAL                  MERSEYSIDE              A                       A
{D43A8B4A-6272-4706-9189-30F8E24EDF13}  210000  23.05.2007 00:00    S                   N       F           BRISTOL                 NORTH SOMERSET          NORTH SOMERSET          A                       A
{3575DAF5-0E80-408F-9970-FDF5D1475E73}  185000  16.11.2007 00:00    S                   N       F           CREWKERNE               SOUTH SOMERSET          SOMERSET                A                       A
{A4246390-61F4-4228-BC82-79D3F369CA34}  32700   12.12.1996 00:00    F                   N       L           SOUTHAMPTON             SOUTHAMPTON             SOUTHAMPTON             A                       A

CREATE TABLE "DWH_PRICE_PAID_RECORDS" ("TRANSACTION_ID" VARCHAR(50) NOT NULL, "PRICE" INTEGER, "DATE_OF_TRANSFER" DATE NOT NULL, "PROPERTY_TYPE" CHAR(1), "OLD_NEW" CHAR(1), "DURATION" CHAR(1), "TOWN_CITY" VARCHAR(50), "DISTRICT" VARCHAR(50), "COUNTY" VARCHAR(50), "PPDCATEGORY_TYPE" CHAR(1), "RECORD_TYPE" CHAR(1));

DWH_POSTCODES

Postcode    Eastings    Northings   Latitude    Longitude           Town            Region          UK_Region   Country_Code    Country_String
AB10        392900      804900      5,713,514   -211,731        Aberdeen            Aberdeen City   Scotland    SCT             Scotland
AB13        385600      801900      5,710,801   -223,776        Milltimber          Aberdeen City   Scotland    SCT             Scotland
AB14        383600      801100      5,710,076   -227,073        Peterculter         Aberdeen City   Scotland    SCT             Scotland
AB21        387900      813200      572,096     -220,033        Aberdeen Airport    Aberdeen City   Scotland    SCT             Scotland
AB22        392800      810700      5,718,724   -211,913        Bridge of Don       Aberdeen City   Scotland    SCT             Scotland
AB30        370900      772900      5,684,678   -247,712        Laurencekirk        Aberdeenshire   Scotland    SCT             Scotland
AB31        368100      798300      5,707,479   -252,623        Banchory            Aberdeenshire   Scotland    SCT             Scotland
AB32        380800      807200      5,715,545   -231,742        Westhill            Aberdeenshire   Scotland    SCT             Scotland
AB33        355200      815100      5,722,464   -274,203        Alford              Aberdeenshire   Scotland    SCT             Scotland
AB34        350800      800600      5,709,393   -281,204        Aboyne              Aberdeenshire   Scotland    SCT             Scotland


CREATE TABLE "DWH_POSTCODES" ("POSTCODE_ID" INTEGER generated always as identity (start with 1 increment by 1), "POSTCODE" VARCHAR(10), "EASTINGS" VARCHAR(50), "NORTHINGS" VARCHAR(50), "LATITUDE" VARCHAR(10), "LONGITUDE" VARCHAR(10), "TOWN" VARCHAR(50) NOT NULL, "REGION" VARCHAR(50), "UK_REGION" VARCHAR(50), "COUNTRY_CODE" VARCHAR(20), "COUNTRY_STRING" VARCHAR(20));

And DIM_REGION

CREATE TABLE "DIM_REGION" ("REGION_ID" INTEGER generated always as identity (start with 1 increment by 1), "TOWN" VARCHAR(30), "COUNTY" VARCHAR(30), "DISTRICT" VARCHAR(30), "LATITUDE" VARCHAR(10), "LONGITUDE" VARCHAR(10), "COUNTRY_STRING" VARCHAR(20));

First I inserted Town(unique), County and District:

INSERT INTO DIM_REGION (TOWN, County, District) SELECT town_city, MAX(county), MAX(district) FROM DWH_PRICE_PAID_RECORDS GROUP BY town_city;

Town has to be unique because I dont have anything else as identifier to match/link the tables. The DIM_REGION table now has 938 unique town records and I want to “UPDATE” the remaining columns “Latitude”, “Longitude” and “COUNTRY_STRING”. The DWH_POSTCODES table has 1637 unique town records which means the towns are different! Only 532 towns are matching and only those shall be updated in the DIM_REGION table.

I’m starting with just one column “Latitude” and this is what I would expect it to look like after the Update statement:

UPDATE DIM_REGION SET DIM_REGION.LATITUDE = (SELECT DWH_POSTCODES.LATITUDE from dim_region join dwh_postcodes on dim_region.town = dwh_postcodes.town where dim_region.town = dwh_postcodes.town);

but when I do that I get this error message (the error message is german and I had to translate, sorry):

ERROR 21000 a scalar subquery returned more than one value

I don’t understand why. If I commit just the select statement:

SELECT DWH_POSTCODES.LATITUDE from dim_region join dwh_postcodes on dim_region.town = dwh_postcodes.town where dim_region.town = dwh_postcodes.town

I get exactly what I expected to get and thats the whole column with all latitudes.

Thank you very much!

Answer

IBM Db2 and Apache Derby DB are completely different products. So, remove the db2 tag.
You provided absolutely non-matched lists of towns in both tables. Was it so hard to prepare matched data sets?

If you don’t have duplicates in the dwh_postcodes.town column, then update should look like this (may be with the same subselect in the EXISTS predicate in WHERE):

UPDATE DIM_REGION SET DIM_REGION.LATITUDE = 
(
SELECT 
--MAX (
DWH_POSTCODES.LATITUDE 
--)
from dwh_postcodes 
where dim_region.town = dwh_postcodes.town
-- fetch first 1 row only
)
/*
WHERE EXISTS 
(
SELECT 1
from dwh_postcodes 
where dim_region.town = dwh_postcodes.town
)
*/
;

Otherwise you have to use some aggregate function (or the fetch first clause if Derby supports it) to make this subselect return only 1 row as shown commented out in the example above.

Alternatively, you could do 1 INSERT instead of INSERT and UPDATE to fill all 4 columns:

INSERT INTO DIM_REGION (TOWN, County, District, latitude) 
SELECT p.town_city, p.county, p.district, d.latitude
FROM (
SELECT town_city, MAX(county) county, MAX(district) district
FROM DWH_PRICE_PAID_RECORDS 
GROUP BY town_city
) p
join dwh_postcodes d on p.town_city = d.town;