Need help on SQL query - Select data with duplicate multiple data entries in one data field

Question

I need to select all data having non-duplicate IDs.. here's my sample table..

----------------------------------------------------------------------------------
ID        | Zip-Code       | Search Query        | ID_LIST
----------------------------------------------------------------------------------
1         | 1000           | Query Sample 1      | 13,14,15,
----------------------------------------------------------------------------------
2         | 2000           | Query Sample 2      | 16,13,17,
----------------------------------------------------------------------------------
3         | 3000           | Query Sample 3      | 18,17,13,
----------------------------------------------------------------------------------
4         | 4000           | Query Sample 4      | 15,16,17,18,
----------------------------------------------------------------------------------
5         | 5000           | Query Sample 5      | 19, 20,

u can notice that IDs 1 and 2 have duplicate, which is 13 on ID_LIST

2 and 3 also have duplicate, which is 13 and 17.

What I want to do is make it like this...

----------------------------------------------------------------------------------
ID        | Zip-Code       | Search Query        | ID_LIST
----------------------------------------------------------------------------------
1         | 1000           | Query Sample 1      | 13,14,15,
----------------------------------------------------------------------------------
2         | 2000           | Query Sample 2      | 16,17,
----------------------------------------------------------------------------------
3         | 3000           | Query Sample 3      | 18,
----------------------------------------------------------------------------------
5         | 5000           | Query Sample 5      | 19,20,

What query would be good for this? Any Help?

13 on ID_LIST is entered in ID no.2, so I dont want to include it in ID no.2 — andil01, Jun 10 '16 at 05:43
[**Is storing a delimited list in a database column really that bad?**](http://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad) — 1000111, Jun 10 '16 at 05:55
You not only do select but also do update. Is it what you want in one query ? — Kaizhe Huang, Jun 10 '16 at 05:55

Utsav · Answer 1 · 2016-06-10T09:56:35.093

Best way to approach it is to normalize your data, as mentioned in comments. But if you absolutely have to do it this way, it would be very difficult to do in query on mysql.

I would suggest you to create a procedure for it. As and when you develop each step, you can google that particular solution of that step, and test it and build up on that. Let me know if any step sound confusing/unclear.

Create a variable string, say v_vals. Initialize with null. At the end of procedure, it will contain all the distinct values of id_list (13,14...20)
Iterate through each row.
Count the number of comma in id_list.
Loop from 1 to number of comma.
In every iteration, use substring and instring to find position of each comma and then extract values from id_list. (13,14...)
use another variable v_id_list. Put null in it.
Search for the values (from step 5) in v_vals. If they exist in v_val, then skip them, else put them in v_val and v_id_list.
Now run an update statement to update id_list with v_id_list.

Now repeat Step 3 to 8 for each row.

Note that v_id_list will be reinitialize for each loop, however v_val will contain all the distinct values of id_list.

Need help on SQL query - Select data with duplicate multiple data entries in one data field

1 Answers1