Perform calculations on MySQL Load Data

Question

I have the following two sql statements

LOAD DATA LOCAL INFILE '~/data/geo_blocks.csv' INTO TABLE geo_blocks FIELDS  ENCLOSED BY '\"'TERMINATED BY ',' LINES TERMINATED BY '\n' (ip_start, ip_end, location_id);

&

update geo_blocks set index_geo = (ip_end - mod(ip_end, 65536));

Is there a way combine the LOAD DATA statement to perform the calculation at the same time?

Answer 1

You should try SET clause in LOAD DATA INFILE command -

LOAD DATA LOCAL INFILE '~/data/geo_blocks.csv'
INTO TABLE geo_blocks
FIELDS ENCLOSED BY '\"'TERMINATED BY ','
LINES TERMINATED BY '\n'
(@ip_start, @ip_end, location_id)
SET index_geo = @ip_end - MOD(@ip_end, 65536);