DuxAres DuxAres - 1 month ago 8
MySQL Question

How to create two tables with first 75% and remaining 25% of another table in MySQL?

I wrote this expecting it would work but

LIMIT
and
OFFSET
doesn't allow me do any calculations by giving error : "Error Code: 1327. Undeclared variable: ceil
"

INSERT INTO as24t
SELECT * FROM as24 LIMIT ceil(count(*)*3/4);
INSERT INTO as24v
SELECT * FROM as24 LIMIT floor(count(*)/4) OFFSET ceil(count(*)*3/4);


All I want to do is take first 3/4 of as24 table and insert into as24t and remaining 1/4 to as24v, considering some tables have odd number of records. Also, I don't want to calculate it and hardcode numbers into query because I have 40+ tables to divide and I feel there must be a clever way.

Answer

Please find something unique to order by in order to verify that you get complement sets.

set @numrows_25=(select count(*) from as24) * 0.25;

PREPARE STMT FROM 'INSERT INTO as24v SELECT * FROM as24 order by ... LIMIT ?';
EXECUTE STMT USING @numrows_25;

PREPARE STMT FROM 'INSERT INTO as24t SELECT * FROM as24 order by ... LIMIT 9999999999 OFFSET ?';
EXECUTE STMT USING @numrows_25;