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.

Improve this question

2 Answers 2

Reset to default
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))
;
Improve this answer
5
  • This sounds like a better idea than mine; can mysqlimport do "update" operations? Maybe with the --replace option? Commented Oct 5, 2018 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. Commented Oct 5, 2018 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. Commented Oct 5, 2018 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. Commented Oct 5, 2018 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. Commented Oct 5, 2018 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;
Improve this answer
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. Commented Oct 5, 2018 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. Commented Oct 5, 2018 at 16:15

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.