1

First of all, I'm aware that there are similar questions out there, but I can't seem to find one that fits my case.

I have a program that updates the stock in a CSV file of about 300 products by checking with our database.

The database has +/- 100k records. At the moment I'm doing a select of all records in the database and just use the ones I need. But I selected 99k records too much.

I could do specific selects for the products too, but I'm worried sending 300 queries (might become more in the future) in a matter of seconds would be too much.

Or another (probably stupid) option:

select * from database where id=product1 || id=product2 
    || id=product3 ||     id=product4,...

What's the better option in this case? I'm not worrying about execution time, I'd rather have clean and "efficient" code than fast code in this scenario.

Lonefish
  • 647
  • 2
  • 11
  • 32
  • 2
    you can provide multiple ids with `SELECT * FROM database WHERE id IN (id1, id2, id3,...)` – Olli Oct 07 '15 at 14:45
  • 1
    Not relevant to the question, but I don't think you should be worried about sending 300 queries. 3 million maybe but not 300. – Rj Geraci Oct 07 '15 at 14:48

2 Answers2

3

You can try like this:

select *
from database where id IN (1, 2, 3)

If the count of values to search is more than the count which is not then do the reverse

select *
from database where id NOT IN (some values)
Rahul Tripathi
  • 168,305
  • 31
  • 280
  • 331
  • Does this also work with strings? Because the field I'm selecting on is a reference, not an ID, just used ID for easy communicating.. – Lonefish Oct 07 '15 at 14:57
  • 2
    @Lonefish: yes it will work with strings as well. make sure to give your strings inside `''` – Rahul Tripathi Oct 07 '15 at 14:58
  • Implemented it, but ran into a problem that I solved in the "huge-query-situation" and run into again now. The list that I get as input contains sometimes only part of the reference (there's a max of 20 chars in the input). Is there a solution along the lines of "where id LIKE IN ('test','othertest', 'thirdtest')"? – Lonefish Oct 07 '15 at 15:29
  • No you can't do a LIKE IN unfortunately, you would have to do OR conditions to achieve that. Or you could potentially try to use regex if there is a common theme. Here is an answer from a different question with the regex http://stackoverflow.com/a/1127106 – Jeff Burgin Oct 07 '15 at 16:22
  • I guess I'm just going to do IN, and create single "LIKE" queries for the products I don't find in the result set, it's only 3 or 4 products in the current set, and I don't see it escalate.. – Lonefish Oct 08 '15 at 06:28
1

You could do something like this:

select *
from database
where id IN (1, 2, 3)

All of your ids that you want to get can just go into that array so you don't have to use a long list of or clauses.

Jeff Burgin
  • 175
  • 10