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'
Advertisement
Answer
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.