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