Update table with chapters, paragraphs and paragraph content using MySql version 8.0.17

comunidadmexicanarom · March 21, 2021, 4:35pm

Hi, I need your help…

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

+-------------------------+-------+-------+-----+
| Contents                | sID_c | sID_p | sID |
+-------------------------+-------+-------+-----+
| Content 1               |     1 | NULL  |   1 |
| - Gaio Giulio Cesare    |     1 | NULL  |   2 |
| Veni, vidi, vici.       |     1 | NULL  |   3 |
| Carpe diem.             |     1 | NULL  |   4 |
| Ubi maior minor cessat. |     1 | NULL  |   5 |
| - Quinto Orazio Flacco  |     1 | NULL  |   6 |
| Condicio sine qua non.  |     1 | NULL  |   7 |
| Carthago delenda est.   |     1 | NULL  |   8 |
| Content 2               |     2 | NULL  |   9 |
| - Marco Porcio Catone   |     2 | NULL  |  10 |
| Inter sidera versor.    |     2 | NULL  |  11 |
| Content 3               |     3 | NULL  |  12 |
| - Marco Tullio Cicero   |     3 | NULL  |  13 |
| Vivere est cogitare     |     3 | NULL  |  14 |
+-------------------------+-------+-------+-----+

In the column Contents of the table t_contents :

The Content X it’s a Section
The - Gaio Giulio Cesare it’s a Chapter
The Veni, vidi, vici , Carpe diem. and Ubi maior minor cessat. are the paragraph of Chapter
etc…

I need update the value on column sID_p according to this scheme, it’s possible?

+-------------------------+-------+-------+-----+
| Contents                | sID_c | sID_p | sID |
+-------------------------+-------+-------+-----+
| Content 1               |     1 | 1     |   1 |
| - Gaio Giulio Cesare    |     1 | 1     |   2 |
| Veni, vidi, vici.       |     1 | 1.1   |   3 |
| Carpe diem.             |     1 | 1.2   |   4 |
| Ubi maior minor cessat. |     1 | 1.3   |   5 |
| - Quinto Orazio Flacco  |     1 | 2     |   6 |
| Condicio sine qua non.  |     1 | 2.1   |   7 |
| Carthago delenda est.   |     1 | 2.2   |   8 |
| Content 2               |     2 | 2     |   9 |
| - Marco Porcio Catone   |     2 | 2     |  10 |
| Inter sidera versor.    |     2 | 2.1   |  11 |
| Content 3               |     3 | 3     |  12 |
| - Marco Tullio Cicero   |     3 | 3     |  13 |
| Vivere est cogitare     |     3 | 3.1   |  14 |
+-------------------------+-------+-------+-----+
11 rows in set (0.02 sec)

The table t_contents is registered in a remote MySql database whose structure cannot be edited because I’m not administrator but user with limited permissions…

I’m not the database designer in this case… I’m sorry… I have this and I have to work on this…

Please don’t blame me for this… better not answer me…

Any help would greatly appreciate… Thank you.

-- ----------------------------
-- Table structure for t_contents
-- ----------------------------
DROP TABLE IF EXISTS `t_contents`;
CREATE TABLE `t_contents`  (
  `Contents` varchar(255) DEFAULT NULL,
  `sID_c` int(11) NULL DEFAULT NULL,
  `sID_p` 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 ('Content 1', 1, NULL, 1);
INSERT INTO `t_contents` VALUES ('- Gaio Giulio Cesare', 1, NULL, 2);
INSERT INTO `t_contents` VALUES ('Veni, vidi, vici.', 1, NULL, 3);
INSERT INTO `t_contents` VALUES ('Carpe diem.', 1, NULL, 4);
INSERT INTO `t_contents` VALUES ('Ubi maior minor cessat.', 1, NULL, 5);
INSERT INTO `t_contents` VALUES ('- Quinto Orazio Flacco', 1, NULL, 6);
INSERT INTO `t_contents` VALUES ('Condicio sine qua non.', 1, NULL, 7);
INSERT INTO `t_contents` VALUES ('Carthago delenda est.', 1, NULL, 8);
INSERT INTO `t_contents` VALUES ('Content 2', 2, NULL, 9);
INSERT INTO `t_contents` VALUES ('- Marco Porcio Catone', 2, NULL, 10);
INSERT INTO `t_contents` VALUES ('Inter sidera versor.', 2, NULL, 11);
INSERT INTO `t_contents` VALUES ('Content 3', 3, NULL, 12);
INSERT INTO `t_contents` VALUES ('- Marco Tullio Cicero', 3, NULL, 13);
INSERT INTO `t_contents` VALUES ('Vivere est cogitare', 3, NULL, 14);

r937 · March 21, 2021, 7:19pm

i’ve been playing with this for over an hour, and i think there’s a problem

why is row 10 - Marco Porcio Catone chapter 2 in Content 2 ? why is there no chapter 1?

so that row 11 Inter sidera versor would be paragraph 1.1 in chapter 1

comunidadmexicanarom · March 22, 2021, 8:49am

Thanks for your help.

I have to solve a problem and I have thought of this solution, but I am willing to follow the instructions of the database experts…

The row 10 is the first chapter of section Content 2, maybe it was better to write

+-------------------------+-------+-------+-----+
| Contents                | sID_c | sID_p | sID |
+-------------------------+-------+-------+-----+
| Content 1               |     1 | 1     |   1 |
| - Gaio Giulio Cesare    |     1 | 1.0   |   2 |
| Veni, vidi, vici.       |     1 | 1.1   |   3 |
| Carpe diem.             |     1 | 1.2   |   4 |
| Ubi maior minor cessat. |     1 | 1.3   |   5 |
| - Quinto Orazio Flacco  |     1 | 2.0   |   6 |
| Condicio sine qua non.  |     1 | 2.1   |   7 |
| Carthago delenda est.   |     1 | 2.2   |   8 |
| Content 2               |     2 | 2     |   9 |
| - Marco Porcio Catone   |     2 | 2.0   |  10 |
| Inter sidera versor.    |     2 | 2.1   |  11 |
| Content 3               |     3 | 3     |  12 |
| - Marco Tullio Cicero   |     3 | 3.0   |  13 |
| Vivere est cogitare     |     3 | 3.1   |  14 |
+-------------------------+-------+-------+-----+

I want to dynamically generate in existent Section ( content field ) a new Chapter using

INSERT INTO `t_contents` ( Contents, sID_c, sID_p, sID )
VALUES
    (
        "- New chapter on existent Section",
        tsID_contents,
        NULL,
        NULL 
    );

and after add new paragraph on this new chapter

INSERT INTO `t_contents` ( Contents, sID_c, sID_p, sID )
VALUES
    (
        "New paragraph on the new chapter",
        tsID_contents,
        NULL,
        NULL 
    );

for this return I have added - New chapter on existent Section and New paragraph on the - New chapter on existent Section

mysql> SELECT * FROM `world_test`.`t_contents` WHERE `sID_c`= 1;
+-----------------------------------+-------+-------+-----+
| Contents                          | sID_c | sID_p | sID |
+-----------------------------------+-------+-------+-----+
| Content 1                         |     1 | NULL  |   1 |
| - Gaio Giulio Cesare              |     1 | NULL  |   2 |
| Veni, vidi, vici.                 |     1 | NULL  |   3 |
| Carpe diem.                       |     1 | NULL  |   4 |
| Ubi maior minor cessat.           |     1 | NULL  |   5 |
| - Quinto Orazio Flacco            |     1 | NULL  |   6 |
| Condicio sine qua non.            |     1 | NULL  |   7 |
| Carthago delenda est.             |     1 | NULL  |   8 |
| - New chapter on existent Section |     1 | NULL  |  15 |
| New paragraph on the new chapter  |     1 | NULL  |  16 |
+-----------------------------------+-------+-------+-----+
10 rows in set (0.03 sec)

the problem is when I try to add a new paragraph to an already existing section, because the new paragraph is not created in the selected chapter but in the last chapter inserted in the database table

for example I add new paragraph on the existent chapter - Gaio Giulio Cesare for this return (row new is the number 17)

+-----------------------------------+-------+-------+-----+
| Contents                          | sID_c | sID_p | sID |
+-----------------------------------+-------+-------+-----+
| Content 1                         |     1 | NULL  |   1 |
| - Gaio Giulio Cesare              |     1 | NULL  |   2 |
| Veni, vidi, vici.                 |     1 | NULL  |   3 |
| Carpe diem.                       |     1 | NULL  |   4 |
| Ubi maior minor cessat.           |     1 | NULL  |   5 |
| New paragraph on the chapter      |     1 | NULL  |  17 |
| - Quinto Orazio Flacco            |     1 | NULL  |   6 |
| Condicio sine qua non.            |     1 | NULL  |   7 |
| Carthago delenda est.             |     1 | NULL  |   8 |
| - New chapter on existent Section |     1 | NULL  |  15 |
| New paragraph on the new chapter  |     1 | NULL  |  16 |
+-----------------------------------+-------+-------+-----+

Instead I have in return

+-----------------------------------+-------+-------+-----+
| Contents                          | sID_c | sID_p | sID |
+-----------------------------------+-------+-------+-----+
| Content 1                         |     1 | NULL  |   1 |
| - Gaio Giulio Cesare              |     1 | NULL  |   2 |
| Veni, vidi, vici.                 |     1 | NULL  |   3 |
| Carpe diem.                       |     1 | NULL  |   4 |
| Ubi maior minor cessat.           |     1 | NULL  |   5 |
| - Quinto Orazio Flacco            |     1 | NULL  |   6 |
| Condicio sine qua non.            |     1 | NULL  |   7 |
| Carthago delenda est.             |     1 | NULL  |   8 |
| - New chapter on existent Section |     1 | NULL  |  15 |
| New paragraph on the new chapter  |     1 | NULL  |  16 |
| New paragraph on the chapter      |     1 | NULL  |  17 |
+-----------------------------------+-------+-------+-----+

The New paragraph on the chapter it’s not stored in the selected chapter ( - Gaius Julius Caesar ) but in the chapter - New chapter on existent Section

How to do resolve this?

r937 · March 22, 2021, 9:11am

either do not use auto_increment for sID and assign the values in the order you want to see them

or (which is probably easier) assign the values of sID_p yourself, when you add content

comunidadmexicanarom · March 22, 2021, 9:13am

Thanks, could you give me an example please?

r937 · March 22, 2021, 2:18pm

of which method? remember, i think the second would be easier – it means you have to assign the sID_p values 1, 1.1, 1.2, 2, 2.1… as you load each row

comunidadmexicanarom · March 22, 2021, 4:27pm

Yes Sir… the second method… how to do assign the sID_p values 1, 1.1, 1.2, 2, 2.1… as you load each row? it’s not clear for me…

r937 · March 22, 2021, 4:39pm

this is how you do it –

INSERT 
  INTO t_contents 
     ( Contents, sID_c, sID_p )
VALUES
     ( 'Content 1'               , 1 , '1  ' )  
    ,( '- Gaio Giulio Cesare'    , 1 , '1.0' )
    ,( 'Veni, vidi, vici.'       , 1 , '1.1' )
    ,( 'Carpe diem.'             , 1 , '1.2' )
    ,( 'Ubi maior minor cessat.' , 1 , '1.3' )
    ,( '- Quinto Orazio Flacco'  , 1 , '2.0' )
    , ...

comunidadmexicanarom · March 22, 2021, 6:37pm

thanks resolved using

WITH cte AS (
  SELECT CONCAT(sID_c, '.', ROW_NUMBER() OVER (PARTITION BY sID_c ORDER BY sID)) sID_p,
         sID
  FROM t_contents
)
UPDATE t_contents t
INNER JOIN cte c ON c.sID = t.sID
SET t.sID_p = c.sID_p

return

mysql> SELECT * FROM `world_test`.`t_contents`;
+-----------------------------------+-------+-------+-----+
| Contents                          | sID_c | sID_p | sID |
+-----------------------------------+-------+-------+-----+
| Content 1                         |     1 | 1.1   |   1 |
| - Gaio Giulio Cesare              |     1 | 1.2   |   2 |
| Veni, vidi, vici.                 |     1 | 1.3   |   3 |
| Carpe diem.                       |     1 | 1.4   |   4 |
| Ubi maior minor cessat.           |     1 | 1.5   |   5 |
| - Quinto Orazio Flacco            |     1 | 1.6   |   6 |
| Condicio sine qua non.            |     1 | 1.7   |   7 |
| Carthago delenda est.             |     1 | 1.8   |   8 |
| Content 2                         |     2 | 2.1   |   9 |
| - Marco Porcio Catone             |     2 | 2.2   |  10 |
| Inter sidera versor.              |     2 | 2.3   |  11 |
| Content 3                         |     3 | 3.1   |  12 |
| - Marco Tullio Cicero             |     3 | 3.2   |  13 |
| Vivere est cogitare               |     3 | 3.3   |  14 |
| - New chapter on existent Section |     1 | 1.9   |  15 |
| New paragraph on the new chapter  |     1 | 1.10  |  16 |
+-----------------------------------+-------+-------+-----+
16 rows in set (0.03 sec)

r937 · March 22, 2021, 7:17pm

that’s simply awesome

did you come up with this on your own? well done!

please note, you would’ve saved me over an hour’s worth of testing if you had relaxed your requirements a little bit in the first place


                                               (A)     (B)
+-----------------------------------+-------+-------+-------+-----+
| Contents                          | sID_c | sID_p | sID_p | sID |
+-----------------------------------+-------+-------+-------+-----+
| Content 1                         |     1 | 1     | 1.1   |   1 |
| - Gaio Giulio Cesare              |     1 | 1     | 1.2   |   2 |
| Veni, vidi, vici.                 |     1 | 1.1   | 1.3   |   3 |
| Carpe diem.                       |     1 | 1.2   | 1.4   |   4 |
| Ubi maior minor cessat.           |     1 | 1.3   | 1.5   |   5 |
| - Quinto Orazio Flacco            |     1 | 2     | 1.6   |   6 |
| Condicio sine qua non.            |     1 | 2.1   | 1.7   |   7 |
| Carthago delenda est.             |     1 | 2.2   | 1.8   |   8 |
| Content 2                         |     2 | 2     | 2.1   |   9 |
| - Marco Porcio Catone             |     2 | 2     | 2.2   |  10 |
| Inter sidera versor.              |     2 | 2.1   | 2.3   |  11 |
| Content 3                         |     3 | 3     | 3.1   |  12 |
| - Marco Tullio Cicero             |     3 | 3     | 3.2   |  13 |
| Vivere est cogitare               |     3 | 3.1   | 3.3   |  14 |
+-----------------------------------+-------+-------+-------+-----+

    (A) = what you originally asked for
    (B) = what you were willing to settle for

system · June 25, 2021, 3:53pm

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