sql - 如何在带有子查询的列中“更新选择”某些值?
问题描述
我正在使用 Apache Derby DB (SQL) 版本 10.14,这是 Derby 参考手册:https ://db.apache.org/derby/docs/10.14/ref/refderby.pdf
我正在尝试创建星型模式,目前正在处理区域维度表。
这些是我正在使用的表: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));
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));
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));
首先我插入城镇(唯一),县和区:
INSERT INTO DIM_REGION (TOWN, County, District) SELECT town_city, MAX(county), MAX(district) FROM DWH_PRICE_PAID_RECORDS GROUP BY town_city;
城镇必须是唯一的,因为我没有其他任何东西作为标识符来匹配/链接表格。DIM_REGION 表现在有 938 个唯一的城镇记录,我想“更新”剩余的列“纬度”、“经度”和“国家字符串”。DWH_POSTCODES 表有 1637 条独特的城镇记录,这意味着城镇是不同的!只有 532 个城镇是匹配的,并且只有那些应该在 DIM_REGION 表中更新。
我从一列“纬度”开始,这是我希望它在 Update 语句之后的样子:
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);
但是当我这样做时,我收到此错误消息(错误消息是德语,我不得不翻译,抱歉):
ERROR 21000 a scalar subquery returned more than one value
我不明白为什么。如果我只提交 select 语句:
SELECT DWH_POSTCODES.LATITUDE from dim_region join dwh_postcodes on dim_region.town = dwh_postcodes.town where dim_region.town = dwh_postcodes.town
我得到了我期望得到的东西,这就是所有纬度的整个专栏。
非常感谢!
解决方案
IBM Db2
并且Apache Derby DB
是完全不同的产品。所以,去掉db2
标签。
您在两个表中都提供了完全不匹配的城镇列表。准备匹配的数据集有那么难吗?
dwh_postcodes.town
如果列中没有重复项,update
则应如下所示(可能与subselect
中的EXISTS
谓词相同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
)
*/
;
否则,您必须使用一些聚合函数(或者fetch first
如果 Derby 支持,则使用该子句)以使其subselect
仅返回 1 行,如上面示例中注释掉的所示。
或者,您可以执行 1 INSERT 而不是 INSERT 和 UPDATE 来填充所有 4 列:
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;
推荐阅读
- django - 姜戈 | 休息 | 添加顶级 JSON 字段中断过滤选项
- java - 使用 Reactor Netty HttpClient,如何将客户端配置为使用 Flux 发布者将多个项目发送到服务器?
- html - 下拉菜单在移动设备上时不显示
- sql - ORACLE - 在大表上设置重复的排名,需要优化
- android - 我能知道输入变量为什么它总是给我错误,非常感谢
- java - 用户输入值后在 Java 中循环
- amazon-web-services - 当 AWS Glue 工作流程完成时,是否可以发布到 SNS?
- javascript - 类似数组/映射的数据结构,具有对项目的唯一 ID 支持、O(1) get() 方法且无重复
- python - 卸载 Python3.8 ,我会丢失所有已安装的软件包吗?
- java - 将日期转换为 LocalDateTime,格式为“yyyy-MM-dd HH:mm:ssXXX”