CODEWITHSUNDEEP
mysql
Lawrance Zhang
Lawrance Zhang

Reputation: 115

Is there anyway to do the insert and delete at the same time?

I have a big table all_records with 100 million records and many columns. I want to insert roughly 30 million records inside this table to another table records_not_used_now based on time-consuming operations of multi columns (the operations are named as f(n) in the following query). Then delete those records from the original table all_records.

The query is like this:

insert into records_not_used_now
select * from all_records  where f(n) > 0;

delete from all_records  where f(n) > 0;

However, as the f(n) > 0 judgment costs computation time, I do not want to do it twice. Is there any query in MySQL that can select the record out of the table and then delete them at the same time? The query helps to avoid running the same judgment and scanning the table twice.

Upvotes: 1

Views: 2648

Answers (2)

sam
sam

Reputation: 1896

As per your explanation, you are moving the some 30 M records from table to another and you want to avoid repetition of f(n) > 0 computation job due to high compute time.

I suggest to do this in 4 specific steps.

  1. Create a temporary table with the select query.
CREATE TEMPORARY_TABLE TABLE IF NOT EXISTS table2 AS (select * from all_records  where f(n) > 0;)

  1. Delete the records in the source table(all_records) using a sql join between this new temporary table and source table

  2. Insert all the records from temporary table to the destination table.

INSERT INTO records_not_used_now SELECT * FROM TEMPORARY_TABLE;
  1. Drop the temporary table
DROP TABLE TEMPORARY_TABLE ;

Refs:

Upvotes: 1

Victorqedu
Victorqedu

Reputation: 494

Create a new column named f_on_n and run

UPDATE all_records   set f_of_n = f(n);
insert into records_not_used_now select * from all_records  where f_of_n > 0;
delete from all_records  where f_of_n > 0;

Upvotes: 0

Related Questions