CODEWITHSUNDEEP
mysqlselectinsert
Joseph
Joseph

Reputation: 290

Mysql inserting from select statement only inserting zeroes

I'm trying to insert a new row in a table with count numbers from select statements from different tables. When I run the following, it creates the row but inserts 0 for the two fields with the select statements (instead of values 1047 and 8 that I get when I run the select statements individually) . I tried putting the select statements inside the insert statement as well but that was throwing errors. Any help on where I'm going wrong? Thanks in advance.

select count(*) from research.Neuro_Recruited_MG as letters_sent;
select count(*) from research.neuro_subject_log s where s.enrolled like 'PS' as pending_screening;
insert into neuro_stats (id, letters_sent, pending_screening)
values (3, 'letters_sent','pending_screening’)

Upvotes: 1

Views: 56

Answers (2)

Ken White
Ken White

Reputation: 125651

You're running three separate statements. The first two (the SELECTs) execute, but starting the next one discards the one before it.

Try something like this instead:

insert into neuro_stats 
  (id, letters_sent, pending_screening)
values 
  (3, 
   (select count(*) from research.Neuro_Recruited_MG),
   (select count(*) from research.neuro_subject_log s where s.enrolled like 'PS'));

Upvotes: 1

Ice
Ice

Reputation: 1172

try, assuming id is auto_increment.

insert neuro_stats(letters_sent, pending_screening)
select sum(count_letters_sent), sum(count_pending_Screening)
from (
  select count(*) as count_letters_sent, 0 as count_pending_screening) 
  from research.neuro_recruited_MG
  UNION
  select 0 as count_letters_sent, count(*) as count_pending_screening
  from reserach.neuro_subject_log as s
  where s.enrolled like '%PS%'
) as xcount;

may be your like 'PS' needs the wildcard character % before and/or after your 'PS' in fact of a search like "beginning", "containing" or "ending" with 'PS'

Upvotes: 0

Related Questions