1

So basically it was given to me a list of around 300 values (numbers). And i need to modify a parameter for all of them. I did a basic query (example below) and i only found 270 from this 300 which was given to me. select count(*) from table where field in('1','2','3','4','5','6');

My question is, how can i see which values (in this case are 30 values) are not present on the table?

This is a live system so i shouldnt create anything there or change.

Thanks for the help in advance.

Apollo Work
  • 11
  • 2

2 Answers2

3

You can add another table holding your set. Let's name it set_table with one column named set_key.

Insert your set into that table; will now look like this:

set_key
----
1
2
...

Now try this

SELECT `set_key` FROM `set_table` WHERE `set_key` not in (select value from your_other_table where 1);

This should give you the keys that are in your set but not in your table.

Example:

Your set is (1,2,42)

Your table contains values with 1 and 2

The subselect select value from your_other_table will give you 1 and 2. The whole query will now look like this: SELECTset_keyFROMset_tableWHEREset_keynot in (1, 2); That'll give you (42) as result.

m02ph3u5
  • 3,022
  • 7
  • 38
  • 51
  • 1
    Totally incorrect. Read question one more time. OP wants to find values from **list** which are **not present in the table**. You're suggesting opposite - find records from table not present in list. – Andrey Korneyev Jul 23 '15 at 13:55
  • Its is not. I already tried that one before making this post. This will give me the remain of values on this table... which are other 300 records... – Apollo Work Jul 23 '15 at 13:58
  • woops- question title confused me, wait a sec – m02ph3u5 Jul 23 '15 at 14:06
  • Thanks, but is it possible to do it without creating a new table on live system? Like somehow creating a virtual table with my list and compare to the full list of the table and see which arent present? – Apollo Work Jul 23 '15 at 14:53
  • @ApolloWork should work w/ temporary tables, too. See http://stackoverflow.com/questions/5859391/create-a-temporary-table-in-a-select-statement-without-a-separate-create-table for temp tables – m02ph3u5 Jul 23 '15 at 14:56
0

Are there any other records in the table besides those 300 ? If not , select count(*) from table where field not in('1','2','3','4','5','6');

Kibadachi
  • 155
  • 6
  • Yes, the full table have around 600 records. – Apollo Work Jul 23 '15 at 13:56
  • Same as answer from m02ph3u5 - you're suggesting opposite to the OP requirement - finding records from table not present in list, but he wants to find values from list which are not present in the table. – Andrey Korneyev Jul 23 '15 at 14:02