Unable to update MySQL SET datatype in a table using REPLACE while referring to SET members using index instead of words

Question

I have the following table my_set_table -

my_set_table | CREATE TABLE `my_set_table` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `my_col` set('C','C++','Java','C#','JavaScript','Kotlin','SQLite','Python') DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |

Here the set members as indexed as follows -

+-----------+---------+--------------+
| Binary    | Decimal | Member       |
+-----------+---------+--------------+
| 0000 0001 | 1       | "C"          |
| 0000 0010 | 2       | "C++"        |
| 0000 0100 | 4       | "Java"       |
| 0000 1000 | 8       | "C#"         |
| 0001 0000 | 16      | "Javascript" |
| 0010 0000 | 32      | "Kotlin"     |
| 0100 0000 | 64      | "SQLite"     |
| 1000 0000 | 128     | "Python"     |
+-----------+---------+--------------+

I populate the table as follows -

INSERT INTO my_set_table (my_col) VALUES
    -> (9),
    -> (36),
    -> (100),
    -> (130),
    -> (128),
    -> (136);

Now, the content of the table is -

SELECT * FROM my_set_table;
+----+--------------------+
| id | my_col             |
+----+--------------------+
|  1 | C,C#               |
|  2 | Java,Kotlin        |
|  3 | Java,Kotlin,SQLite |
|  4 | C++,Python         |
|  5 | Python             |
|  6 | C#,Python          |
+----+--------------------+

Now, I can search for a particular row using index instead of words as follows -

SELECT * FROM my_set_table  
    -> WHERE my_col = 9;
+----+--------+
| id | my_col |
+----+--------+
|  1 | C,C#   |
+----+--------+

Now, if I want to update my table, I can do it using words as follows -

UPDATE my_set_table 
    -> SET my_col = REPLACE(my_col,"C#","Java")
    -> WHERE ID = 1;
SELECT * FROM my_set_table;
+----+--------------------+
| id | my_col             |
+----+--------------------+
|  1 | C,Java             |
|  2 | Java,Kotlin        |
|  3 | Java,Kotlin,SQLite |
|  4 | C++,Python         |
|  5 | Python             |
|  6 | C#,Python          |
+----+--------------------+

But, I intend to do it using indexes rather than words. Thus I replace the words in above query with indexes. But I am unable to get the desired result -

UPDATE my_set_table 
    -> SET my_col = REPLACE(my_col,8,4)
    -> WHERE id = 1;
SELECT * FROM my_set_table;
+----+--------------------+
| id | my_col             |
+----+--------------------+
|  1 | C,C#               |
|  2 | Java,Kotlin        |
|  3 | Java,Kotlin,SQLite |
|  4 | C++,Python         |
|  5 | Python             |
|  6 | C#,Python          |
+----+--------------------+

I even try to match the entire index value and replace it with another index value, but still unsuccessful.

UPDATE my_set_table
    -> SET my_col = REPLACE(my_col,9,5)
    -> WHERE id = 1;
SELECT * FROM my_set_table;
+----+--------------------+
| id | my_col             |
+----+--------------------+
|  1 | C,C#               |
|  2 | Java,Kotlin        |
|  3 | Java,Kotlin,SQLite |
|  4 | C++,Python         |
|  5 | Python             |
|  6 | C#,Python          |
+----+--------------------+

So how can I do update operation using index instead of words?

please see https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad to update what you seek you need to have a map of all combinations or at least find an algorithm but a normalized table stricture will help you keeping the queries simple — nbk, Oct 30 '21 at 21:51
Yeah, I know this is not a normalized table structure, just plain curiosity. If entries can be done using index numbers, why not updates? Actually I am trying out every possible nook and corner in my quest to conquer MySQL :-) — Payel Senapati, Oct 30 '21 at 22:00
you could find an alghorithm for your request, but even with a limited number of elemnts, you have a lot of combinations — nbk, Oct 30 '21 at 22:10
I don't see how the algorithm can be anything difficult, I have already provided with a table where decimal and binary indexes of each set member is provided, to get the required combination just a plain addition is needed — Payel Senapati, Oct 30 '21 at 22:19
then try bills solution for all possible combinations and you will see — nbk, Oct 30 '21 at 22:26
More than Bill's original answer the comment of Bill as to why `REPLACE` and `CONCAT` are not working the way `INSERT` worked is what I found most helpful — Payel Senapati, Oct 30 '21 at 22:52
yes the answer is helpful, bur as i stated only the beginning for an universal solution — nbk, Oct 30 '21 at 23:05

Bill Karwin · Accepted Answer · 2021-10-30T22:10:44.447

1

You can do what you describe with bitwise operations:

mysql> select my_col from my_set_table where id=1;
+--------+
| my_col |
+--------+
| C,C#   |
+--------+

mysql> update my_set_table set my_col = my_col & ~8 | 4 where id = 1;
Query OK, 1 row affected (0.02 sec)

mysql> select my_col from my_set_table where id=1;
+--------+
| my_col |
+--------+
| C,Java |
+--------+

The & ~8 does a bitwise AND against the bitwise complement of 1000 (binary), which is 1111111111111111111111111111111111111111111111111111111111110111. This strips out the bit that represents C++.

The | 4 does a bitwise OR against 100 (binary). This sets the bit for Java.

edited Oct 30 '21 at 22:10

answered Oct 30 '21 at 22:08

Bill Karwin

538,548
86
673
828

this is only a shadow of a general solution – nbk Oct 30 '21 at 22:10
@nbk, you are welcome to post your own answer. – Bill Karwin Oct 30 '21 at 22:11
Excellent answer, but my question is if a simple INSERT statement can take the index values why REPLACE and CONCAT statements are unable to do so? – Payel Senapati Oct 30 '21 at 22:17
1

REPLACE() works on strings, not numbers. So the first argument to REPLACE() is cast as a string, which evaluates as the string 'C,C#'. The digit 8 does not occur in this string, so it doesn't match the substitution pattern. – Bill Karwin Oct 30 '21 at 22:22

Unable to update MySQL SET datatype in a table using REPLACE while referring to SET members using index instead of words

1 Answers1