LinuxQuestions.org
Latest LQ Deal: Linux Power User Bundle
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - General
User Name
Password
Linux - General This Linux forum is for general Linux questions and discussion.
If it is Linux Related and doesn't seem to fit in any other forum then this is the place.

Notices


Reply
  Search this Thread
Old 02-22-2010, 11:35 AM   #1
serverchief
LQ Newbie
 
Registered: Feb 2010
Posts: 1

Rep: Reputation: 0
Smile Scripting: Modifying SQL fields dynamically


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...
 
Old 02-23-2010, 07:53 AM   #2
druuna
LQ Veteran
 
Registered: Sep 2003
Posts: 10,532
Blog Entries: 7

Rep: Reputation: 2392Reputation: 2392Reputation: 2392Reputation: 2392Reputation: 2392Reputation: 2392Reputation: 2392Reputation: 2392Reputation: 2392Reputation: 2392Reputation: 2392
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.
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off



Similar Threads
Thread Thread Starter Forum Replies Last Post
Modifying records of a file using shell scripting barunparichha Linux - Software 5 05-21-2009 10:33 AM
Can I dynamically size HTML input fields? Madone_SL_5.5 Programming 2 12-06-2006 07:32 PM
sql: highest value, comparing fields Ephracis Programming 6 06-01-2005 06:28 PM

LinuxQuestions.org > Forums > Linux Forums > Linux - General

All times are GMT -5. The time now is 12:08 AM.

Main Menu
Advertisement
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration