1

For example, if I have a table structure like this:

Table 1
    ID Name Value

    001 Rajesh 90,100,210,400
    002 Suresh 100,400,300,66
    003 Mahesh 200,500
    004 Virat 400,578,57

How can I delete 400 from Suresh?

DELETE Value ="400" FROM table1
WHERE Name = 'Suresh'

This doesn't work.

Rodia
  • 1,407
  • 8
  • 22
  • 29
XXDebugger
  • 1,581
  • 3
  • 26
  • 48
  • Possible duplicate of: http://stackoverflow.com/questions/14642658/the-best-way-to-remove-value-from-set-field Also see Docs: http://ftp.nchu.edu.tw/MySQL/tech-resources/articles/mysql-set-datatype.html To remove set elements from an existing set, we use the REPLACE function to remove the element. If using decimal values, we use a combination of the bitwise AND operator & with the bitwise NOT operator ~. `UPDATE set_test SET myset = REPLACE(myset,'Dancing','') WHERE rowid = 6; UPDATE set_test SET myset = myset & ~2 WHERE rowid = 6;` – xQbert Jul 13 '15 at 17:03

1 Answers1

1

I would recommend splitting the values into a second table which is related via the person's ID. However, you can use the following query for your current situation:

UPDATE table1
SET Value = TRIM(BOTH ',' FROM REPLACE(CONCAT(',', Value, ','), ',400,', ','))
WHERE Name = 'Suresh'

Here's a SQL Fiddle. For reference, see MySQL's string functions

reaanb
  • 9,806
  • 2
  • 23
  • 37