How to delete rows which used to concatenate a string using MySql

Databases
1

Hello,

In the table t_contents stored an a database MySql version 8.0.17 I have these rows

+--------------------------+----+
| Contents                 | ID |
+--------------------------+----+
| - Gaio Giulio Cesare     |  1 |
| - Quinto Orazio Flacco   |  2 |
| - Marco Porcio Catone    |  3 |
| • Marco Tullio Cicero    |  4 |
| • Publilio Siro          |  5 |
| • Lucrezio               |  6 |
+--------------------------+----+

I need concat the rows when string start for - or • symbol and using DESC SEPARATOR "\n" for this return (e. g. • symbol)

enter image description here

Using this query insert new group_concat rows in table t_contents

enter image description here

After this insert new group_concat rows in table t_contents how to do delete from table t_contents the single row?

+--------------------------+----+
| Contents                 | ID |
+--------------------------+----+
| • Marco Tullio Cicero    |  4 |
| • Publilio Siro          |  5 |
| • Lucrezio               |  6 |
+--------------------------+----+

Any help would greatly appreciate… Thank you.

PS: I’m sorry but if paste codes of query the question not posted…

-- ----------------------------
-- Table structure for t_contents
-- ----------------------------
DROP TABLE IF EXISTS `t_contents`;
CREATE TABLE `t_contents`  (
  `Contents` varchar(255) DEFAULT NULL,
  `sID` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`sID`) USING BTREE
) ENGINE = InnoDB;

-- ----------------------------
-- Records of t_contents
-- ----------------------------
INSERT INTO `t_contents` VALUES ('- Gaio Giulio Cesare', 1);
INSERT INTO `t_contents` VALUES ('- Quinto Orazio Flacco', 2);
INSERT INTO `t_contents` VALUES ('- Marco Porcio Catone', 3);
INSERT INTO `t_contents` VALUES ('• Marco Tullio Cicero', 4);
INSERT INTO `t_contents` VALUES ('• Publilio Siro', 5);
INSERT INTO `t_contents` VALUES ('• Lucrezio', 6);
2

Can I first gently suggest not doing this.

But, assuming you’re commited to shattering normalization of your data…
If your WHERE finds all the rows when SELECT’ing… it’ll find all the rows when DELETE’ing as well…

3

no

at the outset, there are three rows

• foo
• bar
• qux

after the insert, there are four

• foo
• bar
• qux
• foo\n• bar\n• qux

the same WHERE clause, if used for the DELETE, will delete all four rows

1 Like
4

oh right. I didnt notice he was circularly inserting into the same table.

I would… what would I do.
Not do this.
Okay, but if I had to do this, what would I do?

Could just delete where the initial condition matches and the delimiter is not present…but that runs afoul if there’s only one record in the table to start with… so… on the proviso that there exists at least 2 records that were concatenated with the first query, then the above holds true.

Alternatively, if you’re doing this in a backend language, you could catch the inserted row’s ID and tell it to exclude that specific ID from the delete… which would be more ‘bulletproof’. (Or, if you wanted to get slightly more complex and ID is autoincrementing, you could subquery your WHERE to find the MAX(ID) for the matched elements.)

It’s late, so i’m rambling.

1 Like
5

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.