插入選擇和值-有解無憂

錯誤：“子查詢回傳超過 1 個值。當子查詢跟隨 =、!=、<、<=、>、>= 或子查詢用作運算式時，這是不允許的。”

INSERT INTO cid_unidade(co_cid, unid_codigo, tp_agravo)
select
    (select cid_codigo from (values 
        ('Y00'),('Y000'),('Y001'),('Y002' ),('Y003'),('Y004' ),('Y005'),('Y006'),('Y007'),
        ('Y008'),('Y009'),('Y01'),('Y010'),('Y011'),('Y012'),('Y013'),('Y014'),('Y015'),
        ('Y016'),('Y017'),('Y018'),('Y019'),('Y02'),('Y020'),('Y021'),('Y022'),('Y023'),
        ('Y024'),('Y025'),('Y026'),('Y027'),('Y028'),('Y029'),('Y03'),('Y030'),('Y031'),
        ('Y032'),('Y033'),('Y034'),('Y035'),('Y036'),('Y037'),('Y038'),('Y039'),('Y04'),
        ('Y040'),('Y041'),('Y042'),('Y043'),('Y044'),('Y045'),('Y046'),('Y047'),('Y048'),
        ('Y049'),('Y05'),('Y050'),('Y051'),('Y052'),('Y053'),('Y054'),('Y055'),('Y056'),
        ('Y057'),('Y058'),('Y059'),('Y06'),('Y060'),('Y061'),('Y062'),('Y063'),('Y064'),
        ('Y065'),('Y066'),('Y067'),( 'Y068'),( 'Y069'),( 'Y07'),( 'Y070'),( 'Y071'),( 'Y072'),
        ('Y073'),( 'Y074'),( 'Y075'),( 'Y076'),( 'Y077'),( 'Y078'),( 'Y079'),( 'Y08'),( 'Y080'),
        ('Y081'),( 'Y082'),( 'Y083'),( 'Y084'),( 'Y085'),( 'Y086'),( 'Y087'),( 'Y088'),( 'Y089'),
        ('R456'),( 'T742')) t(cid_codigo)
        WHERE cid_codigo NOT IN (SELECT co_cid FROM cid_unidade)),
    '0067', '1'

uj5u.com熱心網友回復：

錯誤很明顯。外部select回傳太多行。您可以將其洗掉：

INSERT INTO cid_unidade(co_cid, unid_codigo, tp_agravo)
     select cid_codigo,   '0067', '1'
     from (values 
        ('Y00'),('Y000'),('Y001'),('Y002' ),('Y003'),('Y004' ),('Y005'),('Y006'),('Y007'),
        ('Y008'),('Y009'),('Y01'),('Y010'),('Y011'),('Y012'),('Y013'),('Y014'),('Y015'),
        ('Y016'),('Y017'),('Y018'),('Y019'),('Y02'),('Y020'),('Y021'),('Y022'),('Y023'),
        ('Y024'),('Y025'),('Y026'),('Y027'),('Y028'),('Y029'),('Y03'),('Y030'),('Y031'),
        ('Y032'),('Y033'),('Y034'),('Y035'),('Y036'),('Y037'),('Y038'),('Y039'),('Y04'),
        ('Y040'),('Y041'),('Y042'),('Y043'),('Y044'),('Y045'),('Y046'),('Y047'),('Y048'),
        ('Y049'),('Y05'),('Y050'),('Y051'),('Y052'),('Y053'),('Y054'),('Y055'),('Y056'),
        ('Y057'),('Y058'),('Y059'),('Y06'),('Y060'),('Y061'),('Y062'),('Y063'),('Y064'),
        ('Y065'),('Y066'),('Y067'),( 'Y068'),( 'Y069'),( 'Y07'),( 'Y070'),( 'Y071'),( 'Y072'),
        ('Y073'),( 'Y074'),( 'Y075'),( 'Y076'),( 'Y077'),( 'Y078'),( 'Y079'),( 'Y08'),( 'Y080'),
        ('Y081'),( 'Y082'),( 'Y083'),( 'Y084'),( 'Y085'),( 'Y086'),( 'Y087'),( 'Y088'),( 'Y089'),
        ('R456'),( 'T742')) t(cid_codigo)
        WHERE cid_codigo NOT IN (SELECT co_cid FROM cid_unidade));

請注意，常量值包含在select.

轉載請註明出處，本文鏈接：https://www.uj5u.com/caozuo/415078.html

標籤：

上一篇：SQL根據from和to日期查詢出生日期和月份

下一篇：添加缺失的資料