-2

I have a record in table column like this (1001,1002,1003,1004,1005) and I want to delete "1003" from this list. Please help me.

Anoop R Desai
  • 712
  • 5
  • 18
Muhammad Saad
  • 21
  • 4
  • 5
    Don't store data as comma separated items. It will only cause you lots of trouble. – jarlh Dec 19 '18 at 11:22
  • there is no `record` type in mysql – Oto Shavadze Dec 19 '18 at 11:24
  • 1
    There are a variety of string functions to help you modify a string: https://dev.mysql.com/doc/refman/8.0/en/string-functions.html Ultimately you need to (1) read the string from your data, (2) modify the string value per whatever logic you need to define, (3) update the record to store the new string. (You'll find that normalizing the data would make this *a lot* easier. Any time you have more than one value in a column, you or a colleague have *probably* done something wrong.) – David Dec 19 '18 at 11:27
  • Indeed, as others have alluded to, your data structure is not logical. The values you've shown should not be all in one field. A field is supposed to store a _single value_ Instead create a second table which has the foreign key back to your main table to associate it. That's how proper relational databases are structured. Perhaps you should study database design concepts more thoroughly before you continue to work on your database. – ADyson Dec 19 '18 at 11:45
  • Possible duplicate of [Is storing a delimited list in a database column really that bad?](https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad) – Madhur Bhaiya Dec 19 '18 at 11:59

2 Answers2

0

For your own good, don't store data like this. This type of issue is not, by far, the biggest problem you will run into.

This being said, you can solve you issue by using:

UPDATE TABLE
SET COLUMN = REPLACE (COLUMN, ',1003,', ',')
WHERE ID = PK;
João Santos
  • 43
  • 6
0

even this will work:

   update tablename set column=(select 
   substr(column,1,instr(colname,',',2))||susbtr(column,instr(column,',',3),length(column)- 
   instr(column,',',3)) from tablename where id=value;
Nikhil S
  • 3,786
  • 4
  • 18
  • 32