Append text to each of multiple lines in file

Question

I have a query which is to be performed on thousands of rows (28,000 odd to be more exact) using a unique identifier. This is (simplified) the query:

update "table1" set event_type = 'NEW' where id=

And there is a file ids.txt which contains the ids for the rows the update is to be performed on:

10003
10009
....
....
79345
79356

The resultant file should be like this:

update "table1" set event_type = 'NEW' where id=10003;
update "table1" set event_type = 'NEW' where id=10009;
...
update "table1" set event_type = 'NEW' where id=79356;

Other than taking the ids.txt file and using vim to form all the 28000 queries using global string substitution, is there an easy way to do this?

Answer 1

Use something simple like sed:

sed -r 's/^([0-9]*)$/update "table1" set event_type = \'NEW\' where id=\1/' file
               |     write back using \1 as placeholder
             catch digits in the file

Previous answer

I used an approach based on bash loops, whereas it is a bit overkilling. See a related question: Why is using a shell loop to process text considered bad practice?

Loop like this:

while read id
do
   echo "update \"table1\" set event_type = 'NEW' where id=${id};"
done < ids.txt

It outputs:

$ while read id; do echo "update \"table1\" set event_type = 'NEW' where id=${id};"; done < ids
update "table1" set event_type = 'NEW' where id=10003;
update "table1" set event_type = 'NEW' where id=10009;
...
update "table1" set event_type = 'NEW' where id=79345;
update "table1" set event_type = 'NEW' where id=79356;

Answer 2

Writing a shell loop is always the wrong approach when just manipulating text.

All you need in this case is a simple sed subsitution:

$ cat file
10003
10009
$ sed 's/.*/update "table1" set event_type = '\''NEW'\'' where id=&;/' file
update "table1" set event_type = 'NEW' where id=10003;
update "table1" set event_type = 'NEW' where id=10009;

Answer 3

sed -i "s/^/update \"table1\" set event_type = 'NEW' where id=/; s/$/;/" id.txt

The -i makes the change inline (actually changes the file id.txt).

The s/^/.../ substitutes the start of each line (^) with ...

The s/$/.../ subsittutes the end of t each line ($) with ...

Answer 4

Not global string substitution but from inside vim you could use

:%norm Iupdate "table1" set event_type = 'NEW' where id=<ESC>A;

where <ESC> is Ctrl+v+esc