Обсуждение: insert into table

Поиск
Список
Период
Сортировка

insert into table

От
Yambu
Дата:
hi

may i know why the below mysql is not inserting into table analyze_checks



insert into analyze_checks(dbname,table_name,frag_ratio, days,needs_optimization,needs_analyzing )
(select
'test' as dbname,
table_name,
frag_ratio,
days,
needs_optimization,
needs_analyzing
from
(select
table_name,
cast(frag_ratio as decimal(5,2)) as frag_ratio,
days,
case when frag_ratio > 1 then 'Yes' else 'No' end as needs_optimization,
case when days > -1 then 'Yes' else 'No' end as needs_analyzing
from (
select
t.ENGINE,
concat(t.TABLE_SCHEMA, '.', t.TABLE_NAME) as table_name,
round(t.DATA_FREE/1024/1024, 2) as data_free,
(t.data_free/(t.index_length+t.data_length)) as frag_ratio,
datediff(now(), last_update) as days
FROM information_schema.tables t
left join mysql.innodb_table_stats s on t.table_name=s.table_name
WHERE DATA_FREE > 0 ORDER BY frag_ratio DESC )d ) d
where needs_optimization='Yes' or needs_analyzing='Yes');

Re: insert into table

От
Scott Ribe
Дата:
this is a PostgreSQL mailing list, not a MySQL mailing list




Re: insert into table

От
"David G. Johnston"
Дата:
On Fri, May 28, 2021 at 9:22 PM Yambu <hyambu@gmail.com> wrote:
may i know why the below mysql is not inserting into table analyze_checks
insert into analyze_checks(dbname,table_name,frag_ratio, days,needs_optimization,needs_analyzing )
(select


Since I don't have the benefit of seeing an error message I'm going to presume the parentheses surrounding the select query are being rejected.

David J.

Re: insert into table

От
Yambu
Дата:
Hi David

There is no error, it just says zero rows affected, but when i run select it brings results

On Sat, May 29, 2021 at 6:29 AM David G. Johnston <david.g.johnston@gmail.com> wrote:
On Fri, May 28, 2021 at 9:22 PM Yambu <hyambu@gmail.com> wrote:
may i know why the below mysql is not inserting into table analyze_checks
insert into analyze_checks(dbname,table_name,frag_ratio, days,needs_optimization,needs_analyzing )
(select


Since I don't have the benefit of seeing an error message I'm going to presume the parentheses surrounding the select query are being rejected.

David J.