0

I have a data file called 'frequency.txt' in two columns, column 1 is 'frequency' it is a number showing a repetition of corresponding column 2 which has ids .

Example:-

Data

frequency, id

 32    329
312    330
320    331
132    332
232    334
 52    336
 42    337
 82    338
 ..    ...     

The above data has to be updated in a column in a mysql database. There are more than 100 lines and everyday I have to update it. Right now I am using this command to update it manually.

echo ' update table.id_set set 'frequency' = 32 whereid=329; '|mysql -B -u username -p -h database.com

How to write a shell script which will automatically replace the value of 'frequency' and 'id' in the above echo command and run it for all the existing lines in the frequency.txt

PS:- The number of lines in the frequency.txt will be different.

1

You can use mysqlimport or call directly LOAD DATA INFILE sql statement (via mysql).

mysqlimport doesn't like "fixed-width" data files but you can see Loading fixed-width, space delimited .txt file into mySQL for an example on how to handle them.

Example

This example relies on the supposition that the column id is the primary key of the table id_set.

LOAD DATA LOCAL INFILE '/some/path/data.txt' 
REPLACE INTO TABLE id_set
(@row)
SET frequency = TRIM(SUBSTR(@row,1,7)),
    id = TRIM(SUBSTR(@row,8,50))
;
5
  • This sounds like a better idea than mine; can mysqlimport do "update" operations? Maybe with the --replace option?
    – Jeff Schaller
    Oct 5 '18 at 10:54
  • Yes, it is the goal of that option. The manual page states If you specify REPLACE, input rows replace existing rows. In other words, rows that have the same value for a primary key or unique index as an existing row.
    – andcoz
    Oct 5 '18 at 13:19
  • but id's position might change, for example a new 'id'=33 might be added later on into database. So, will this be able to replace frequency for the corresponding id? id is integer in this case, but I have to do similar work for ids as characters also.
    – CCC
    Oct 5 '18 at 15:38
  • Can you show an example using above method. I tried and messed up the data but thankfully I tried it on a mock data.
    – CCC
    Oct 5 '18 at 15:54
  • 1
    The replace policy is based on table primary key definition. The order in the file is irrelevant. All depends on how you defined the primary key on the table.
    – andcoz
    Oct 5 '18 at 15:54
0

I don't have mysql at hand to test with, but if it can accept multiple SQL statements:

awk '{print "update table.id_set set frequency = "$1" where id = "$2";"}' < input | 
  mysql -B -u username -p -h database.com

On the sample input, the awk command would send this to the mysql command:

update table.id_set set frequency = 32 where id = 329;
update table.id_set set frequency = 312 where id = 330;
update table.id_set set frequency = 320 where id = 331;
update table.id_set set frequency = 132 where id = 332;
update table.id_set set frequency = 232 where id = 334;
update table.id_set set frequency = 52 where id = 336;
update table.id_set set frequency = 42 where id = 337;
update table.id_set set frequency = 82 where id = 338;
2
  • The above idea works well, thank you. Why did you say the below 'mysqlimport' is better than your method? I am just curious if there is any technical advantage.
    – CCC
    Oct 5 '18 at 15:53
  • @CCC I think the fewer tools that you use in a toolchain, the less risk you run of breakage. If you can write the file in a way that mysql can read in natively, you remove the dependence on a shell script and awk to do more transformations. It happens rarely enough, but in this case, I appreciate andcoz pointing out that such a tool exists.
    – Jeff Schaller
    Oct 5 '18 at 16:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.