CODEWITHSUNDEEP
mysqlsqlinsert
Tom
Tom

Reputation: 21

Insert into not inserting for mysql

I have below insert into statement , for mysql. When i run select statement alone it brings results.

Create statement for table is below

**create table if not exists analyze_checks(dbname varchar(50),table_name  varchar(50),frag_ratio decimal, days integer,needs_optimization char(3),needs_analyzing char(3)) ENGINE=INNODB**

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

Upvotes: 0

Views: 37

Answers (1)

matigo
matigo

Reputation: 1461

There is no need to wrap the SQL statement used for the INSERT in brackets. Heck, the whole query can be simplified like this:

INSERT INTO analyze_checks(dbname,table_name,frag_ratio, days,needs_optimization,needs_analyzing ) 
SELECT 'test' AS dbname,
       tmp.table_name, 
       CAST(tmp.frag_ratio AS DECIMAL(5,2)) AS frag_ratio, 
       tmp.days,
       CASE WHEN tmp.frag_ratio > 1 THEN 'Yes' ELSE 'No' END AS needs_optimization,    
       CASE WHEN tmp.days > -1 THEN 'Yes' ELSE 'No' END as needs_analyzing 
  FROM (SELECT 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) tmp
 WHERE 'Yes' = CASE WHEN tmp.frag_ratio > 1 THEN 'Yes' 
                    WHEN tmp.days > -1 THEN 'Yes'
                    ELSE 'No' END
 ORDER BY frag_ratio DESC;

Brackets and derived tables are fine when used in moderation 🤐

Upvotes: 1

Related Questions