Solaris

Hi,
Is there any way that mysql query reads the content from a text file which has data in the below format:
1,2,3,4,5
and selects matched data from another table.
ie I have a table named xyz which has same ids as in that file.
I want this query to get count of the ids from xyz file by matching the ids.

At mysql prompt i run this command which gets me the count from xyz table.
mysql>select count(*) from xyz where id in (1,2,3,4,5);
Now i want the sql query to read the file and match these ids with ids in xyz table and get me the count.

No
Its not possible.
Mysql client only reads from the db and not from just any text file. You would have to use a mediator like perl to fetch data from both sources and then compare.

---------- Post updated at 04:27 PM ---------- Previous update was at 04:27 PM ----------

No
Its not possible.
Mysql client only reads from the db and not from just any text file. You would have to use a mediator like perl to fetch data from both sources and then compare.

I want to write a script which will connect to db and then in this script i give a query which shld read this data,match and get me the count.
can u tell me how will i use mediator like perl to get the count with some eg.

If it's really just this one line, use

Code:

echo "select count(*) from xyz where id in ($( cat test.txt ));" | mysql -u ....

cool one but a lot of struggle for further processing.
regards.

Thanks!! that was really exact what i wanted,without any struggle i could get the output which i required.
That was really helpful.

Of course, you could also go an use the line from your other thread and create something compact but unreadable like

Code:

perl -le 'print "SELECT COUNT(*) FROM xyz WHERE id IN (".(join ",", map { chomp; $_ } <>).");";' file | mysql -u ....