INSERT INTO SELECT AND VALUES-CodePudding

Error: "Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."

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'

CodePudding user response：

The error is pretty clear. The outer select returns too many rows. You can just remove it:

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));

Note that the constant values are included in the select.