Data manipulation in a mysql table

Question

I have 2 columns in the table which have incorrect entries. The size of the table runs in to billions of records. I had like to swap data between two columns (c1 and C2) The approach taken is to export the data in small chunks in to CSV files and then import it back with corrected entries. For example, below is the data set

--------
|C1 | C2 |
|3  | 4  |
|4  | 6  |

I would then export the data in to a semicolon delimted CSV file (complete command NOT shown) as below

SELECT C2,C1 FROM TABLE temp INTO OUTFILE /tmp/test.csv

The output of such command would be

4;3
6;4

When I import back the data (after deleting the data in question), the data will be corrected as follows

  | C1  C2 |
  | 3   4  |
  | 4   6  |

It is really a matter of OUTFILE and INFILE operation, I believe

Question

Does the approach makes sense? The real data also expects NULL, int values in some of the columns apart from data swaps.
The other complexity is in the production database, I will need to use the WHERE clause. The table name would also be fetched dynamically.
With reference to point 2, how do I add dynamicity to the queries. Should I use a STORED procedure or SHELL SCRIPT? STORED Procedure does not seem to support LOAD DATA INFILE functionality.
If I am left with shell, any sample script that I can reuse? The CSV filename, table name and WHERE clause will have to be built at run time.
Also the size of the chunk to be exported and imported will be calculated dynamically.
Any other approach?

Note - This is a INFOBRIGHT column based table on top of mysql. The UPDATE query is non-performant and ALTER TABLE is not supported by INFOBRIGHT.

1) This is extremely broad question and the answers will likely to be primarily opinion based. 2) There is no question here. 3) Load data infile is not allowed in stored procedures. 4) Again, a too broad question. It is unlikely that anyone would write you a script from scratch. 5) Again, there is no question here 6) This is basically the same as 1) So, I'm not really clear what kind of answer you are expecting here. — Shadow, Mar 21 '16 at 18:31
Just use a new table in the database to copy your data to. After you have converted all your data, drop the original and rename the new table to the original — Norbert, Mar 21 '16 at 18:38

score 0 · Answer 1 · answered Mar 21 '16 at 19:27

you can use this approach create a temporary table 'temp_table' and the use this procedure, then call it from anywhere with the name of the table like this.

call change_fields('table_origen');

the procedure works like this

delete the data in the temp_table
insert into this temp_table with the order changed from the table_origen
delete from the table_origen
insert into the table_origen the information from the temp_table

You can go crazy and how many variables you want to accept

CREATE DEFINER=`root`@`%` PROCEDURE `change_fields`(IN `tableName` VARCHAR(200))
    LANGUAGE SQL
    NOT DETERMINISTIC
    CONTAINS SQL
    SQL SECURITY DEFINER
    COMMENT ''
BEGIN

    SET @hr1 = CONCAT('Delete from temp_table;');

    PREPARE hrStmt1 FROM @hr1;
    EXECUTE hrStmt1;
    DEALLOCATE PREPARE hrStmt1;
    SET @hr1 = CONCAT('insert into temp_table (C_1, C_2) Select C2, C1 from  `',tableName,'`;');

    PREPARE hrStmt1 FROM @hr1;
    EXECUTE hrStmt1;
    DEALLOCATE PREPARE hrStmt1;

    SET @hr1 = CONCAT('Delete from `',tableName,'`;');

    PREPARE hrStmt1 FROM @hr1;
    EXECUTE hrStmt1;
    DEALLOCATE PREPARE hrStmt1;

    SET @hr1 = CONCAT('insert into `',tableName,'` (C1,C2) select C_1, C_2 from  temp_table;');

    PREPARE hrStmt1 FROM @hr1;
    EXECUTE hrStmt1;
    DEALLOCATE PREPARE hrStmt1;

END;

Data manipulation in a mysql table

1 Answers1