Removing the entries in a comma separated field based on the data of another table

Question

First off I am using MySQL 5.0.96. (always forget to mention that)

I have two tables(TMP_VIN_STOCK and newvehicles) I load TMP_VIN_STOCK with our current inventory of vehicles daily.

newvehicles is our currently listed vehicles on our website table.

I want to find results in newvehicles that are NOT in TMP_VIN_STOCK. (So I can remove them from website because we sold them)

TMP_VIN_STOCK.name and newvehicles.stocknum are each tables stock number column.

TMP_VIN_STOCK.name has only single values of a stock number newvehicles.stocknum can have multiple values separated by commas in it. (Same exact vehicle just multiple stock numbers)

Example:

TMP_VIN_STOCK table

- K12049
- S12040
- T12020

newvehicles table

- S12018, S12039, S12040
- Y13068, Y13093
- T12020

How can I find out what stock numbers are not in TMP_VIN_STOCK but are in newvehicles?

this is a very bad database design, do you want it fixed or you want to stick with this? — jurgenreza, Apr 25 '13 at 22:40

jurgenreza · Answer 1 · 2013-07-15T22:27:11.423

Your problem beautifully demonstrates why tables in relational databases should be normalized.

If you want to keep this design then you have to do something like this:

loop over the newvehicles rows:
    loop over the items in the comma separated field
        check if the item does not exist in TMP_VIN_STOCK

Not to mention that mysql does not have a function to split a comma separated list and let alone the pain you should go through to remove the ones you find from the comma separated list.

You should definitely redesign your database. Have a table for vehicle data and another one for stock data and link them with foreign keys. That way you don't have to go through all this pain. You can retrieve the data you want with a simple query.

Also if an entry is not in the stock, its data should not be deleted but the count should be zero or it should be flagged as out of stock.

score 0 · Answer 2 · edited May 23 '17 at 11:49

This question has some details that would let you split the values in newvehicles table and let you populate a (temp) table with the results then a not exists or not in query would give the results you need.

Alternatively you could keep the previous days load of TMP_VIN_STOCK (maybe add a date field into the table - then you could compare what you loaded today with what you loaded yesterday

Removing the entries in a comma separated field based on the data of another table

TMP_VIN_STOCK table

newvehicles table

2 Answers2