Variable definition inside mysql query

Hello,
I am running ubuntu16.04.
By means of @Rudic's help, I have below command.
What I need to do is to replace video_id by value of video_id in which WHERE clause is matched:

{print  "INSERT INTO video_series_files (id, video_id, file_type, protocol, \ 
url, languages, quality, accessed ,status )" \
        " SELECT '." ++CNT ".', video_id, 'sub', 'custom', \
'http://localhost/test/" $1 "." $2 "." $3 "', 'a:1:{i:0;s:2:'" $2 \
        "';}', '', '1', '1' FROM video_series_files WHERE url like '%" $1 "%.mkv';"}

video_id is the name of column, video_id should be the value inside the same row when "WHERE" query matches.

I assigned variable as @video_id and then tried to grab the value_id but no luck :

{print  "INSERT INTO video_series_files (id, video_id, file_type, protocol, url, languages, \
quality, accessed ,status )"  \
      " SELECT '." ++CNT ".', @video_id, 'sub', 'custom', \
'http://localhost/test/" $1 "." $2 "." $3 "', 'a:1:{i:0;s:2:'" $2 \
        "';}', '', '1', '1' FROM video_series_files WHERE url like '%" $1 "%.mkv' \
AND SET @video_id=video_id IN (SELECT video_id FROM video_series_files \
WHERE url like '%$COL1%.mkv') ;" }

I'd appreciate your help
Thank you
Boris

replace means update not insert ???

Hello Wassi,
I add new row , so insert is the correct command for this case

Kind regards
Boris

That "set variable" seems to be too much effort. On the other hand, my mysql is non existent.

Please show the result of

mysql -uroot -D mydatabase -pmypasswd  -sN -e "SELECT video_id FROM video_series_files WHERE url like '%Mr.Smith.Goes.to.Washington%.mkv';"

Hello Rudic,
It gives:

658

Thank you
Boris

Then, with this single number result,

SELECT '.105.', video_id, 'sub', 'custom', 'http://localhost/test/Mr.Smith.Goes.to.Washington.en.srt', 'a:1:{i:0;s:2:"en";}', '', '1', '1' FROM video_series_files WHERE url like '%Mr.Smith.Goes.to.Washington%.mkv';

should yield one record that can be inserted immediatedly. What result do you get from this select statement? Where are you stuck? What does not work?

Hello,
When I run it with multiple files as you guided yesterday, output barisprep.sql seems like (just excerpted related field):

INSERT INTO video_series_files (id, video_id, file_type, protocol, url, languages, quality, \
accessed ,status ) SELECT '.1381.', video_id, 'sub', 'custom', 'http://localhost/test/How.To.Steal.A.Mi$
INSERT INTO video_series_files (id, video_id, file_type, protocol, url, languages, quality, \
accessed ,status ) SELECT '.1381.', video_id, 'sub', 'custom', 'http://localhost/test/Human.Traffic.de.$
INSERT INTO video_series_files (id, video_id, file_type, protocol, url, languages, quality, \
accessed ,status ) SELECT '.1381.', video_id, 'sub', 'custom', 'http://localhost/test/I.Am.Heath.Ledger

Instead of bold video_id in line including SELECT , I am trying to insert value of related video_id grabbed from the table for related $1 at each line....

Let's say
If video_id of Mr.SmithGoes.to.Washington.mkv is 655, I am trying to replace video_id text shown in bold as 655
If video_id of Human.Traffic.mkv is 1233, I am trying to replace video_id text shown in bold as 1233

Like This:

SELECT '.105.', '.655.', 'sub', 'custom', 'http://localhost/test/Mr.Smith.Goes.to.Washington.en.srt', 'a:1:{i:0;s:2:"en";}', '', '1', '1' FROM video_series_files WHERE url like '%Mr.Smith.Goes.to.Washington%.mkv';

Thank you
Boris

The bold video_id IS the column value from the table.

Do you REALLY want the string constant '.655.' (with two enclosing dots), or the integer video ID?

What is the output of your above select , and what if '.655.' is replaced by video_id ?

No sure whether or not it will make a difference, but maybe you can give a try enclosing column name in back tick like `column_name`

I think I've found the root cause.
When I was about to post this thread, I supposed that the code was not grabbing video_id from related line as I received error. Now I see I have to change the lasting part of the code :

....WHERE url like '%" $1 "%.mkv';"}

like

....WHERE url like '" $1 "%.mkv';"}

or:

....WHERE url like '" $1 ".mkv';"}

Let's say: if $1 is It , the code finds many matching lines in '%It%.mkv'
If the value in $1 is This.is.how.we.do.it.mkv , it returns with error as there are double entries.

I will post the result later on as my database needs to be repaired.

Thank you
Boris

Voila!
Changed the last part with exact match like shown below and no need to create a second query to grab video_id :
.....WHERE url = 'http://localhost/test/" $1 ".mkv';"}

What I still do not know is how it grabs video_id as value in SELECT field.
I did not tell it to read video_id where $1 matches.
I supposed that I should have created a second query to grab it which was the main purpose of the thread.

Thank You All
Boris

I was about to ask why you were using the LIKE condition...

No? What is the result of these select s:

SELECT  video_id FROM video_series_files WHERE url = 'Mr.Smith.Goes.to.Washington.mkv';
SELECT  video_id, 'sub', 'custom' FROM video_series_files WHERE url = 'Mr.Smith.Goes.to.Washington.mkv';
 SELECT '.105.', video_id, 'sub', 'custom', 'http://localhost/test/Mr.Smith.Goes.to.Washington.en.srt', 'a:1:{i:0;s:2:"en";}', '', '1', '1' FROM video_series_files WHERE url = 'Mr.Smith.Goes.to.Washington.mkv';

Hello Rudic,
Without testing, I can say that it will not return any result because there should be http://% like this:
url = 'http://%Mr.Smith.Goes.to.Washington%.mkv';
Maybe I am wrong..

If the last occupied row id is 100, I run it like this:
barisprep

CNT=100
{print  "INSERT INTO video_series_files (id, video_id, file_type, protocol, url, languages, \
quality, accessed ,status )"
        " SELECT '." ++CNT ".', video_id, 'sub', 'custom', \
'http://localhost/test/" $1 "." $2 "." $3 "', 'a:1:{i:0;s:2:\"" $2 "\";}', '', '1', '1' \
FROM video_series_files WHERE url = 'http://localhost/" $1 ".mkv';"}

Then I run:

awk -vCNT=$counter -fbarisprep 2 > barisprep.sql

Next:

grep -A0 "INSERT" barisprep.sql > 1.sql
sed -i '/--/d' 1.sql
cat 1.sql |  awk '/101/{count++; { sub("101",count+100, $0)}; }; {print }' > barisprep.sql
mysql -uroot -D mydatabase -pmypasswd -sN  < barisprep.sql

Thank you
Boris