2

I have this table :

mysql> desc mytab;
+------------+-------------+------+-----+---------+----------------+
| Field      | Type        | Null | Key | Default | Extra          |
+------------+-------------+------+-----+---------+----------------+
| id         | int(11)     | NO   | PRI | NULL    | auto_increment |
| value      | varchar(50) | YES  |     | NULL    |                |
| template   | varchar(50) | YES  |     | NULL    |                |
| difference | tinyint(1)  | YES  |     | NULL    |                |
+------------+-------------+------+-----+---------+----------------+

containing this sample data :

mysql> select * from mytab;
+----+-------+----------------+------------+
| id | value | template       | difference |
+----+-------+----------------+------------+
|  1 | 7     | |2|5|7|8|9|12| |       NULL |
|  2 | 0     | |5|            |       NULL |
|  3 | 5     | |2|5|7|8|9|12| |       NULL |
+----+-------+----------------+------------+
3 rows in set (0.00 sec)

I want to set difference field to FALSE if template contains '|value|' and to TRUE if not.

so the result would be :

mysql> select * from mytab;
+----+-------+----------------+------------+
| id | value | template       | difference |
+----+-------+----------------+------------+
|  1 | 7     | |2|5|7|8|9|12| |       0    |
|  2 | 0     | |5|            |       1    |
|  3 | 5     | |2|5|7|8|9|12| |       0    |
+----+-------+----------------+------------+
3 rows in set (0.00 sec)
2

I found this solution :

mysql> update mytab set difference=(template not like concat('%|',value,'|%'));
Query OK, 0 rows affected (0.05 sec)
Rows matched: 3  Changed: 0  Warnings: 0

mysql> select * from  mytab;
+----+-------+----------------+------------+
| id | value | template       | difference |
+----+-------+----------------+------------+
|  1 | 7     | |2|5|7|8|9|12| |          0 |
|  2 | 0     | |5|            |          1 |
|  3 | 5     | |2|5|7|8|9|12| |          0 |
+----+-------+----------------+------------+
3 rows in set (0.00 

sec)

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.