General table and update related table using MySQL 8

Databases
1

Hello,

I have these two table on MySQL database version 8.0.12 (localhost)

general_table

----------------------------------------------------------
| N_rec     | Related | unique_number | status_rec | pID |
----------------------------------------------------------
| 2023-0097 | Y       |         98679 | Main       |  97 |
| 2023-0201 | Y       |         45311 | Lower      | 201 |
----------------------------------------------------------

And

related_table
-------------------------------------------------------------------
| unique_number | N_rec_main | status_rec_main  | N_rec     | pID |
-------------------------------------------------------------------
|         45311 | 2023-0097  | Main             | 2023-0201 | 201 |
-------------------------------------------------------------------

From the general table it is possible to associate two rows that are similar.

In this example the N_rec number 2023-0201 has been associated as a lower row of N_rec number 2023-0097, defined as main on the general table.

Now I need insert into table related_table this row:

----------------------------------------------------------
| N_rec     | Related | unique_number | status_rec | pID |
----------------------------------------------------------
| 2023-0097 | Y       |         98679 | Main       |  97 |
----------------------------------------------------------

And update related_table for this output:

------------------------------------------------------------------------
| unique_number | N_rec_main      | status_rec       | N_rec     | pID |
------------------------------------------------------------------------
|         45311 | 2023-0097       | Main             | 2023-0201 | 201 |
|         98679 | 2023-0201       | Lower            | 2023-0097 |  97 |
------------------------------------------------------------------------

I have tried using

INSERT INTO `related_table` ( unique_number, N_rec, status_rec, N_rec ) SELECT
unique_number,
N_rec,
status_rec,
N_rec
FROM
    `general_table` 
WHERE
    pID = 97;

But the output not what I expected

 ------------------------------------------------------------------------
 | unique_number | N_rec_main      | status_rec       | N_rec     | pID |
 ------------------------------------------------------------------------
 |         98679 | 2023-0097       | Main             | 2023-0097 |  97 |
 |         45311 | 2023-0097       | Main             | 2023-0201 | 201 |
 ------------------------------------------------------------------------

Any help?

2

Why would status_rec for that row be Lower, when you’ve put the status_rec as Main? Why would the N_rec_main be 2023-0201, when in your inserted row it’s 2023-0097?

The database did exactly what you asked it to do. You gave it bad input.

3

Thanks for reply.

Because I need update the table related_table adding also the main row from general table.

If update the table related_table adding also the main row, the old row on the table related_table must become

------------------------------------------------------------------------
| unique_number | N_rec_main      | status_rec       | N_rec     | pID |
------------------------------------------------------------------------
|         98679 | 2023-0201       | Lower            | 2023-0097 |  97 |
------------------------------------------------------------------------
4

But that’s not the old row?

You showed us:

so the “old” row is 45311.
the new row is 98679.

Your query says:

“Go look at the general table for pID 97. Get me the unique_number, the N_rec, and the status_rec from that table, and put them into the related table as-is.”

So the database went to your general table, looked at what you have there:

and put those values into the related table.

I think you may have confused yourself because the related_table entry for 45311 is incorrect.

5

Sorry but looks like you never heard of something like “JOIN” in databases? Otherwise I cannot explain myself why you are storing all data redundant in both tables.

You only need the main unique id in the reference table. You do not need the main_status, not the main record number. also you do not need the pID of the Lower in the second table as it is already stored in the first table.

Clean up your database design or you will have really big problems in the future.

6

Yes, probably I’m confused because it’s a job that others have left me…
Now they asked me this…
If you have any suggestions they are really appreciated

basically I always have to add to the table related_table, the main row from which the lower row was selected… as an example… thank you

8

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