Error Code: 1242. Subquery returns more than 1 row

hesmarjassi · Janeiro 14, 2016

Alguém sabe me dizer o pq ele não está aceitando mais que um no retorno, uso todos os dias essa rotina e nunca havia dado esse erro??

update   agendamento_fila_espera
set dt_saida =  ( select saida_gud
                   from controle_horario_saida as c
                  where c.Tu = agendamento_fila_espera.tu
                    and c.Entrada = agendamento_fila_espera.dt_entrada) 
 where year(dt_prev) = 2016
 and agendamento_fila_espera.fg_status = 'F' 
   and ( month(dt_prev) =  01)
   and tu not in (select tu from vw_duplicado)
    and tu in ( select tu from controle_horario_saida where tu = agendamento_fila_espera.tu )

lokaodomau · Janeiro 14, 2016

Provavelmente seja por causa da primeira subquery, que pode estar retornando mais de um valor. Mas antes de dar certeza, poderia colar o erro que ocorre?

hesmarjassi · Janeiro 14, 2016

Acredito que é mais de um valor mesmo, mas será q nunca retornou mais de um, é q sempre usei, mas o erro é esse:

Error Code: 1242. Subquery returns more than 1 row

Motta · Janeiro 14, 2016

Este select deve retornar as linhas vilãs.

select tu, entrada 
from controle_horario_saida as c
group by tu ,entrada
having count(*) > 1

hesmarjassi · Janeiro 14, 2016

Retornou vários registros mesmo, sendo assim eu não vou conseguir fazer o meu update ? Olha o q retornou:

	0005858279	2014-10-03 06:47:14
	0005858288	2014-10-02 22:26:13
	0005858308	2014-10-02 02:10:23
	0005862261	2014-10-02 21:58:35
	0005862288	2014-10-07 08:46:52
	5800150	        2014-09-08 11:17:59
	5889293	        2014-10-21 13:01:27
	6435380	        2015-09-24 09:34:53
	6592176	        2015-12-30 17:16:04
	CL-BH-0019	2014-04-24 23:23:04
	CL00002019	2014-03-25 05:48:32
	CL00002020	2014-03-25 09:51:54
	CL00002029	2014-03-25 16:05:56
	CL00002243	2014-05-14 10:37:44
	CL00002677	2014-09-25 13:17:29
	CL00002762	2014-10-27 07:11:12
	CLEXC039	2015-09-14 06:29:50
	CLEXC041	2015-09-14 13:02:52
	CLEXC069	2015-09-16 11:49:41
	CLEXC081	2015-09-17 07:46:53
	CLEXC147	2015-09-30 01:22:10
	clexc150	2015-09-29 14:05:16
	CLGA001	        2014-09-19 11:24:38
	CLMG0028	2014-11-03 17:12:49
	CLOTM2107	2014-04-27 09:52:44
	CLOTM2125	2014-04-29 10:42:46
	CLOTM2734	2014-10-22 10:33:46
	CLOTM2983	2014-11-22 08:37:51
	CLOTM3003	2014-12-01 04:33:18
	CLOTM3527	2015-05-23 01:37:07
	CLOTM3556	2015-05-25 02:41:30
	CLOTM3568	2015-06-16 18:46:43
	CLOTM3575	2015-06-19 09:26:28
	CLOTM3603	2015-06-17 19:01:57
	CLOTM3690	2015-07-12 06:46:45
	CLOTM3705	2015-07-12 10:26:47
	CLOTM3731	2015-07-12 11:09:10
	CLOTM3862	2015-08-26 09:36:18
	CLSP0550	2014-12-04 10:35:45
	CLSP0788	2015-05-21 09:13:36
	clsp10047	2015-09-29 10:00:21
	CLSP10052	2015-09-29 13:11:38
	CLSP10057	2015-09-30 08:54:26
	CLSP10058	2015-09-30 07:47:05
	CLSP2056	2015-10-22 10:36:04
	CL_OTM_224	2014-05-13 12:44:50
	CL_OTM_237	2014-06-18 15:37:20
	CL_SPT_000	2014-05-22 06:31:15
	COF057	        2015-09-19 09:09:21
	cof120	        2015-09-21 09:18:09
	cof156	        2015-09-24 22:17:16
	COF159	        2015-09-24 20:40:09
	COF176	        2015-09-26 07:33:46
	COF188	        2015-09-27 15:32:05
	COFE0035	2015-07-20 06:48:58
	COFE0094	2015-07-19 10:37:46
	cofe0157	2015-07-23 09:36:50
	COFE0173	2015-07-20 15:35:00
	OXIDO	        2015-06-19 10:19:05
	OXIDO00011	2016-01-12 15:43:22
	OXIDO0065	2015-08-28 09:15:05
	P000165389	2014-02-01 16:19:57
	P000165605	2014-02-03 02:57:46
	P000165789	2014-02-01 13:08:12
	P000165915	2014-02-02 06:33:43
	P000168721	2014-02-17 16:49:36
	P000168727	2014-02-14 19:01:46
	P000169086	2014-02-17 10:05:24
	P000169154	2014-02-15 20:45:52
	P000169943	2014-02-19 19:24:17
	P000170491	2014-02-22 04:15:15
	P000170534	2014-02-21 19:16:38
	P000171837	2014-02-27 09:33:48
	P000172274	2014-02-27 18:45:44
	P000172384	2014-03-03 08:59:32
	P000172427	2014-03-05 13:53:25
	P000173122	2014-03-04 12:38:31
	P000173131	2014-03-06 13:53:52
	P000173468	2014-03-07 14:35:54
	P000173810	2014-03-08 04:38:35
	p000173862	2014-03-10 18:39:53
	P000173883	2014-03-09 08:46:05
	P000174515	2014-03-11 21:16:01
	P000174790	2014-03-14 21:14:22
	P000175023	2014-03-14 16:13:38
	P000175072	2014-03-13 01:31:24
	P000175119	2014-03-15 19:45:32
	P000175222	2014-03-15 10:00:42
	P000175394	2014-03-15 08:55:17
	P000176301	2014-03-24 07:32:59
	P000176303	2014-03-22 05:09:51
	P000176321	2014-03-20 19:50:23
	P000176370	2014-03-20 10:01:31
	P000176372	2014-03-20 06:03:49
	P000176382	2014-03-19 19:29:12
	P000176774	2014-03-20 16:08:21
	P000176919	2014-03-22 09:49:59
	P000177279	2014-03-22 07:14:08
	P000177876	2014-03-28 14:51:32
	P000177929	2014-03-25 09:50:09
	P000178063	2014-03-27 00:18:16
	P000178107	2014-03-26 16:16:16
	P000178114	2014-03-24 17:52:00
	P000178241	2014-03-25 15:44:30
	P000178341	2014-03-25 20:04:17
	P000179370	2014-03-28 14:39:35
	P000179660	2014-03-31 06:23:08
	P000180218	2014-04-01 17:27:12
	P000180253	2014-04-04 14:47:42
	P000180266	2014-04-03 13:01:32
	P000180704	2014-04-03 13:39:31
	P000181456	2014-04-08 02:53:08
	P000181520	2014-04-08 07:20:19
	P000181544	2014-04-08 04:00:09
	P000181578	2014-04-08 03:21:57
	P000181688	2014-04-10 10:14:32
	P000181835	2014-04-09 09:26:28
	P000182253	2014-04-10 21:38:41
	P000182421	2014-04-11 09:59:37
	P000182537	2014-04-11 20:14:18
	P000182655	2014-04-14 06:01:43
	P000182963	2014-04-14 18:44:23
	P000183106	2014-04-15 03:30:57
	P000183142	2014-04-15 00:43:44
	P000183228	2014-04-16 23:33:17
	P000183847	2014-04-17 11:41:13
	P000184198	2014-04-19 16:32:20
	P000184388	2014-04-23 00:28:06
	P000184496	2014-04-23 01:55:44
	P000185913	2014-04-26 10:39:10
	P000185995	2014-04-26 04:42:15
	P000186371	2014-05-01 02:18:18
	P000186487	2014-04-28 16:01:01
	P000186800	2014-05-08 03:03:01
	P000187286	2014-04-30 09:28:53
	P000187932	2014-05-09 19:55:36
	P000188076	2014-05-13 21:08:03
	P000188300	2014-05-05 09:30:44
	P000188380	2014-05-06 05:07:52
	P000190443	2014-05-14 01:36:08
	P000190672	2014-05-19 08:50:08
	P000191345	2014-05-21 12:47:50
	P000191389	2014-05-22 07:51:12
	P000191972	2014-05-20 14:34:10
	P000192398	2014-05-24 11:23:17
	P000192422	2014-05-25 03:12:56
	P000192559	2014-05-23 21:09:12
	P000192616	2014-05-22 20:56:41
	P000192689	2014-05-24 00:09:33
	P000192956	2014-05-23 22:40:37
	P000193292	2014-05-27 18:46:23
	P000193479	2014-05-27 06:25:20
	P000193773	2014-05-29 10:40:23
	P000194254	2014-05-29 20:02:15
	P000194636	2014-05-31 13:59:03
	P000195011	2014-06-04 05:46:56
	P000195418	2014-06-03 11:05:07
	P000195682	2014-06-04 10:31:36
	P000196079	2014-06-06 09:08:42
	P000196097	2014-06-06 13:35:44
	P000196486	2014-06-07 10:24:42
	P000196789	2014-06-10 01:00:10
	P000197038	2014-06-12 14:15:56
	P000197834	2014-06-16 18:54:00
	P000197837	2014-06-16 15:31:03
	P000198475	2014-06-20 06:43:02
	P000198855	2014-06-24 00:18:48
	P000198974	2014-06-24 01:20:43
	P000200733	2014-06-30 22:58:19
	P000200979	2014-06-29 11:17:46
	P000200985	2014-06-29 10:21:24
	P000201463	2014-06-30 22:38:18
	P000208354	2014-08-01 18:26:18
	P000208433	2014-08-01 11:46:20
	P000208848	2014-08-04 21:52:35
	P000209461	2014-08-07 06:47:09
	P000210057	2014-08-13 00:26:33
	P000210148	2014-08-09 16:10:27
	P000211191	2014-08-19 23:26:42
	p000211233	2014-08-17 18:57:37
	P000211252	2014-08-17 19:30:25
	P000211457	2014-08-23 09:34:58
	P000211516	2014-08-19 19:28:42
	P000211989	2014-08-21 09:12:30
	P000212278	2014-08-23 10:46:45
	P000213738	2014-09-01 10:23:56
	P000214063	2014-09-24 16:09:01
	P000214589	2014-09-05 19:30:03
	P000216524	2014-09-13 01:43:49
	p000217481	2014-09-18 23:46:50
	P000217607	2014-09-18 21:44:34
	p000218182	2014-09-26 01:31:08
	P000218308	2014-09-24 16:40:02
	P000218704	2014-09-24 21:43:32
	P000219756	2014-09-29 19:28:53
	P000219956	2014-09-30 03:55:36
	P000221123	2014-10-05 00:17:03
	P000221309	2014-10-06 22:22:55
	P000222106	2014-10-09 19:17:22
	P000222132	2014-10-11 06:12:19
	P000222452	2014-10-10 19:08:21
	P000223470	2014-10-21 13:35:20
	p000223733	2014-10-16 09:15:19
	P000224131	2014-10-18 18:41:56
	P000224388	2014-10-21 11:49:01
	P000224406	2014-10-21 01:08:23
	P000224472	2014-10-18 18:54:37
	P000225303	2014-10-22 16:12:45
	P000225721	2014-10-25 08:53:48
	P000226314	2014-10-27 16:05:59
	P000226674	2014-10-28 23:46:41
	P000226906	2014-10-28 21:43:17
	P000228621	2014-11-05 11:14:34
	P000229054	2014-11-06 20:51:27
	P000229232	2014-11-11 12:30:05
	P000229233	2014-11-11 07:54:15
	P000229270	2014-11-14 05:34:00
	P000229621	2014-11-09 16:40:04
	p000229891	2014-11-12 14:00:42
	P000229903	2014-11-11 08:11:54
	P000230866	2014-11-19 00:13:47
	P000230935	2014-11-17 04:26:02
	P000231222	2014-11-18 13:59:55
	P000231259	2014-11-21 20:04:09
	P000231376	2014-11-18 02:39:57
	P000232024	2014-11-20 10:44:11
	P000232472	2014-11-22 03:33:06
	P000233308	2014-11-27 05:58:42
	P000233458	2014-11-27 12:08:59
	P000233703	2014-11-27 07:55:37
	P000233949	2014-11-28 13:03:57
	P000234997	2014-12-03 00:48:29
	P000235446	2014-12-05 08:29:28
	P000236024	2014-12-09 17:40:52
	P000236130	2014-12-08 13:09:22
	P000236759	2014-12-10 15:36:32
	P000237905	2014-12-16 01:48:22
	P000238283	2014-12-20 13:28:04
	P000238442	2014-12-18 05:54:58
	P000239460	2014-12-24 10:37:28
	P000239461	2014-12-24 08:53:29
	P000239462	2014-12-24 11:20:17
	P000239600	2014-12-30 13:04:14
	P000239871	2014-12-29 16:23:12
	P000248019	2015-02-04 13:56:24
	P000248466	2015-02-07 01:12:26
	P000248644	2015-02-15 21:56:56
	P000249122	2015-02-12 10:48:21
	P000250048	2015-02-15 21:47:55
	P000250088	2015-02-18 08:37:40
	P000250100	2015-02-19 13:51:36
	P000250202	2015-02-17 03:30:06
	P000270872	2015-05-23 14:55:05
	P000271297	2015-05-23 17:38:13
	P000271969	2015-05-27 03:34:46
	P000272208	2015-05-28 18:40:33
	P000272209	2015-05-27 19:37:35
	P000272349	2015-05-29 05:20:29
	P000275279	2015-06-16 06:40:42
	P000275638	2015-06-16 08:41:25
	P000275847	2015-06-18 06:33:36
	P000278005	2015-06-27 16:48:59
	P000280250	2015-07-11 03:25:03
	P000280459	2015-07-20 20:41:07
	P000281329	2015-07-17 12:32:31
	P000281350	2015-07-17 19:11:41
	P000281459	2015-07-20 06:33:11
	P000281923	2015-07-23 04:57:48
	P000282761	2015-07-25 10:45:17
	P000287894	2015-08-24 06:19:03
	P000289581	2015-08-31 20:08:43
	P000291460	2015-09-17 06:43:25
	p000291962	2015-09-17 16:10:43
	P000292420	2015-09-17 23:33:29
	P000292669	2015-09-19 15:15:21
	P000292844	2015-09-20 15:00:59
	P000292848	2015-09-24 03:01:26
	P000293593	2015-09-27 10:16:18
	p000293706	2015-09-23 22:44:06
	p000293909	2015-09-25 03:45:53
	P000294170	2015-09-27 08:45:47
	P000294264	2015-09-27 10:47:24
	P000294322	2015-09-28 22:29:25
	P000298286	2015-10-22 17:05:28
	P000298442	2015-10-22 18:59:44
	P000298810	2015-10-23 03:39:32
	P000306126	2015-11-30 09:33:52
	P000312050	2016-01-11 12:30:38
	P000586384	2014-10-06 20:27:01
	SUCATA EM 	2015-10-21 07:57:49

O campo tu é um varchar de 12 e o campo Entrada um Datetime

Motta · Janeiro 14, 2016

O que se precisa atualizar ?

Talvez uma agregadora como max resolva o problema.

hesmarjassi · Janeiro 14, 2016

Preciso atualizar os horários da tabela controle_horario_saida na tabela agendamento_fila_espera

update   agendamento_fila_espera
set dt_saida =  ( select saida_gud
                   from controle_horario_saida as c
                  where c.Tu = agendamento_fila_espera.tu
                    and c.Entrada = agendamento_fila_espera.dt_entrada) 
 where year(dt_prev) = 2016
 and agendamento_fila_espera.fg_status = 'F' 
   and ( month(dt_prev) =  01)
   and tu not in (select tu from vw_duplicado)
    and tu in ( select tu from controle_horario_saida where tu = agendamento_fila_espera.tu )

Motta · Janeiro 14, 2016

O MAX nao resolve ?

hesmarjassi · Janeiro 14, 2016

Não tenho muito conhecimento, mas verifiquei e a sintaxe esta correta, ???

update   agendamento_fila_espera
set dt_saida =  ( select  MAX  (saida_gud)
                   from controle_horario_saida as c
                  where c.Tu = agendamento_fila_espera.tu
                    and c.Entrada = agendamento_fila_espera.dt_entrada) 
 where year(dt_prev) = 2016
 and agendamento_fila_espera.fg_status = 'F' 
   and ( month(dt_prev) =  01)
   and tu not in (select  tu from vw_duplicado)
    and tu in ( select   tu from controle_horario_saida where tu = agendamento_fila_espera.tu )

Erro:

Error Code: 1630. FUNCTION ormec.MAX does not exist. Check the 'Function Name Parsing and Resolution' section in the Reference Manual

Motta · Janeiro 14, 2016

Parece correta.

hesmarjassi · Janeiro 14, 2016

Assim funciona :

update   agendamento_fila_espera
set dt_saida =  ( select  MAX  (saida_gud)
                   from controle_horario_saida as c
                  where c.Tu = agendamento_fila_espera.tu
                    and c.Entrada = agendamento_fila_espera.dt_entrada LIMIT 1) 
 where year(dt_prev) = 2016
 and agendamento_fila_espera.fg_status = 'F' 
   and ( month(dt_prev) =  01)
   and tu not in (select  tu from vw_duplicado)
    and tu in ( select   tu from controle_horario_saida where tu = agendamento_fila_espera.tu )

Mas isso não é correto ? Usar o Limit ? Eu perderei registros ?

Arquivado

Error Code: 1242. Subquery returns more than 1 row

Recommended Posts

hesmarjassi 0

Compartilhar este post

Link para o post

Compartilhar em outros sites

lokaodomau 61

Compartilhar este post

Link para o post

Compartilhar em outros sites

hesmarjassi 0

Compartilhar este post

Link para o post

Compartilhar em outros sites

Motta 645

Compartilhar este post

Link para o post

Compartilhar em outros sites

hesmarjassi 0

Compartilhar este post

Link para o post

Compartilhar em outros sites

Motta 645

Compartilhar este post

Link para o post

Compartilhar em outros sites

hesmarjassi 0

Compartilhar este post

Link para o post

Compartilhar em outros sites

Motta 645

Compartilhar este post

Link para o post

Compartilhar em outros sites

hesmarjassi 0

Compartilhar este post

Link para o post

Compartilhar em outros sites

Motta 645

Compartilhar este post

Link para o post

Compartilhar em outros sites

hesmarjassi 0

Compartilhar este post

Link para o post

Compartilhar em outros sites

Javascript - Calcular Valores Monetário em Tempo Real

JavaScript - Exibir logo dentro da Tabela html

PHP+Codeigniter - Implementar o Input CÓDIGO ou ID do Produto

Fóruns

Tempo Real

Informação importante