Shell Programming and Scripting

This is a mysql night mare that I cant seem to wrap my head around. Any shell based answers is appreciated as I dont know Perl and all I would do would be blindly copy & paste !

FILE CONTENTS -
1389685 INSERT INTO Opera_ShirtCatlog(col1,col2) VALUES (1,'TEST1'),(2,'TEST2');

1389675 INSERT INTO Opera_ShirtCatlog_Wom(col1, col2, col3) VALUES (9, 'Siz12, FormFit', 'Test');

DESIRED OUTPUT -

INSERT INTO Opera_ShirtCatlog(col1,col2,FIRSTCOL) VALUES (1, 'TEST', 1389685);
INSERT INTO Opera_ShirtCatlog_Wom(col1, col2, col3, FIRSTCOL) VALUES (9, 'Siz12 FormFit', 'Test', 1389675);

As you can see above, the task at hand is this that I want to move the first column as part of the INSERT statement's LAST COLUMN before the closing paranthesis. I am loosing my mind trying to figure this out as I can use sed to hold the variable and then how am I supposed to substitute this only especially when it has to avoid the first closing bracket as thats table definition ... also if I use awk then how do I do a regex substitution ? Any help is sincerely appreciated.

regards,
Manohar.

It is unclear to me what you mean by "the task at hand is this that I want to move the first column as part of the INSERT statement's LAST COLUMN before the closing paranthesis."

Where does FIRSTCOL come from?

Fpmurphy,

At the outset thanks for posting reply. Sorry for not being clear at the outset.

In the input file, the first column is the value that I need to be moved inside the paranthesis after VALUES. In the two example line I mentioned, for the first line, 1389685 is the value that should be moved into the insert statement after the string VALUES as below -

Try this:

Code:

perl -lpe 's/^(\d+) (.*?)(\);)/$2, $1$3/g' file 
INSERT INTO Opera_ShirtCatlog(col1,col2) VALUES (1,'TEST1'),(2,'TEST2', 1389685);

INSERT INTO Opera_ShirtCatlog_Wom(col1, col2, col3) VALUES (9, 'Siz12, FormFit', 'Test', 1389675);

Hope this gets you on the right path.

in2nix4life,

Thank you for extending your arms in helping me. First, I dont understand PERL and second, its not moving that at all...

Any sed / awk based answer is requested from you.

regards,

What OS, shell, type of file containing the data?

The more information provided, the better chance of working towards a satisfactory solution.

Its a regular mysql log file and I am running bash. Also I did happen to mention early in my post that I am looking for awk/sed based solution as I dont understand PERL. Also, This is not the original file itself & after writing couple of filters, this is where it is....

gave me the following -

NOw, when I tag your PERL liner, you can see below I got nothing -

Please advise !