Scripting: Modifying SQL fields dynamically

serverchief · 02-22-2010, 10:35 AM

Hi all,

I'm looking for a way to dynamically modify a fied in the SQL file.

Example,

INSERT INTO `TABLE` VALUES(1AADFDF,DFF33D,10023,SOMEDATA, SOMEDATA);
INSERT INTO `TABLE` VALUES(1AADFDF,DFF33D,10098,SOMEDATA, SOMEDATA);
INSERT INTO `TABLE` VALUES(1AADFDF,DFF33D,10123,SOMEDATA, SOMEDATA);
INSERT INTO `TABLE` VALUES(1AADFDF,DFF33D,10983,SOMEDATA, SOMEDATA);INSERT INTO `TABLE` VALUES(1AADFDF,DFF33D,4022,SOMEDATA, SOMEDATA);
INSERT INTO `TABLE` VALUES(1AADFDF,DFF33D,20456,SOMEDATA, SOMEDATA);
....
INSERT INTO `TABLE` VALUES(1AADFDF,DFF33D,32123,SOMEDATA, SOMEDATA);

The third field in this SQL file is what needs to change to n+1

"10023" needs to change to "1"
"10098" needs to change to "2"
"10123" needs to change to "3"
"10983" needs to change to "4"
etc...

I tried with awk and sed, but since both require the usage of ' (single quote), my variable fails to get updated. I know i can use cut and recreate the sql statememnt, but i believe there should be easier way using sed or awk.

Would anyone know of any creative way to get this to work in one-liner? This needs to be a part of the script preferable without making call to external file.

Thanks for your help...

druuna · 02-23-2010, 06:53 AM

Hi,

Would this help:

awk 'BEGIN { FS="," ; OFS="," } { print $1, $2, NR, $4, $5 } ' sql.infile

Test run:

Code:

 $ cat sql.infile
INSERT INTO `TABLE` VALUES(1AADFDF,DFF33D,10023,SOMEDATA, SOMEDATA);
INSERT INTO `TABLE` VALUES(1AADFDF,DFF33D,10098,SOMEDATA, SOMEDATA);
INSERT INTO `TABLE` VALUES(1AADFDF,DFF33D,10123,SOMEDATA, SOMEDATA);
INSERT INTO `TABLE` VALUES(1AADFDF,DFF33D,10983,SOMEDATA, SOMEDATA);
INSERT INTO `TABLE` VALUES(1AADFDF,DFF33D,4022,SOMEDATA, SOMEDATA);
INSERT INTO `TABLE` VALUES(1AADFDF,DFF33D,20456,SOMEDATA, SOMEDATA);
INSERT INTO `TABLE` VALUES(1AADFDF,DFF33D,32123,SOMEDATA, SOMEDATA);

$ awk 'BEGIN { FS="," ; OFS="," } { print $1, $2, NR, $4, $5 } ' sql.infile 
INSERT INTO `TABLE` VALUES(1AADFDF,DFF33D,1,SOMEDATA, SOMEDATA);
INSERT INTO `TABLE` VALUES(1AADFDF,DFF33D,2,SOMEDATA, SOMEDATA);
INSERT INTO `TABLE` VALUES(1AADFDF,DFF33D,3,SOMEDATA, SOMEDATA);
INSERT INTO `TABLE` VALUES(1AADFDF,DFF33D,4,SOMEDATA, SOMEDATA);
INSERT INTO `TABLE` VALUES(1AADFDF,DFF33D,5,SOMEDATA, SOMEDATA);
INSERT INTO `TABLE` VALUES(1AADFDF,DFF33D,6,SOMEDATA, SOMEDATA);
INSERT INTO `TABLE` VALUES(1AADFDF,DFF33D,7,SOMEDATA, SOMEDATA);

The above prints all fields (I see 5 in your, hopefully relevant example), but instead of printing field number 3 ($3) it prints the record number. The records seem to be in sequential order. There will be holes if empty lines are present in the infile.

I did make one assumption: There's one line in your example that holds 2 records instead of one.
If this is not a typo (which I assumed), you need to fix this first (sed 's/);[ ]*I/);\rI/g' infile, should do that trick).

Hope this helps.