×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

MySQL Query for incrementing field from duplicates in database

MySQL Query for incrementing field from duplicates in database

MySQL Query for incrementing field from duplicates in database

(OP)
First, hi to all
I've the following problem:
I have in an existing MySQL-DB duplicates of records and i need to increment the column "proofNr" n+1 for every entry. Please refer the last row in the following resultset.

mysql> SELECT * FROM `korrektur` WHERE `auftrag`= 140802;
+--------+---------+------------+-------------+----------------+------------+-------------+-----------+---------+-----------+
| ID | auftrag | ausgang | ausgang_von | ausgang_medium | eingang | eingang_von | druckfrei | proofNr |
+--------+---------+------------+-------------+----------------+------------+-------------+-----------+---------+-----------+
| 187649 | 140802 | 2020-09-01 | 29 | 1 | 2020-09-08 | 29 | NULL | | 1 | -> OK
| 187783 | 140802 | 2020-09-08 | 29 | 1 | 2020-10-08 | 29 | NULL | | 1 | -> 2
| 188247 | 140802 | 2020-10-08 | 29 | 1 | 2020-10-19 | 29 | NULL | | 1 | -> 3
| 188395 | 140802 | 2020-10-19 | 29 | 1 | NULL | NULL | NULL | | 1 | -> 4
+--------+---------+------------+-------------+----------------+------------+-------------+-----------+---------+-----------+
4 rows in set (0,03 sec)

I hope you see what I mean. Does anyone have an idea? I would appreciate every hint or tip.
Thank you in advance!
kaeser

RE: MySQL Query for incrementing field from duplicates in database

Hi

As the solution would be to use row_number(), it also needs window functions :

CODE --> MySQL

select
*,
row_number() over w proofNr_correct

from korrektur

window w as (partition by auftrag) 

Or if your goal is to update the table with those values, then common table expressions may also come handy :

CODE --> MySQL

with cte as (
    select
    ID,
    row_number() over w proofNr_correct

    from korrektur

    window w as (partition by auftrag)
)
update korrektur
join cte on cte.ID = korrektur.ID

set korrektur.proofNr = cte.proofNr_correct 

Note that all auftrag groups are handled in a single query. If still need it only for 140802, just put back the [tt]where clause. ( No, you can not use row_number() without all that stuff. )

Of course, assuming you have MySQL 8.0, as the used features were introduced there.

Feherke.
feherke.github.io

RE: MySQL Query for incrementing field from duplicates in database

(OP)
First of all thank you feherke. And sorry about my late answer: I had a bad toothache evil
I adopted your example to:

CODE --> MySQL

WITH cte AS (SELECT ID, ROW_NUMBER() OVER w proofNr_correct FROM korrektur_copy WINDOW w AS (PARTITION BY ID)) UPDATE korrektur_copy JOIN cte ON cte.ID = korrektur_copy.ID SET korrektur_copy.proofNr = cte.proofNr_correct WHERE auftrag = 140802; 
So far all goes well. But the result set says the following:

CODE --> MySQL

Query OK, 0 rows affected (0.59 sec)
Rows matched: 4  Changed: 0  Warnings: 0 
As you can see: nothing happend to the database!
Where do I make the mistake? I can't find it out.
Thanks for any help.
kaeser

RE: MySQL Query for incrementing field from duplicates in database

Hi

Quote (kaeser)

Where do I make the mistake?
Here : (PARTITION BY ID). That partition by is roughly like a group by. When using window functions, the data set is split on chunks by the partition by expression, then the functions are applied for each chunk separately, so each will have for example its own min() or max() value. And similarly in our case row_number() will number each chunk's rows separately, restarting from 1 for each. As semantically id fields used to contain unique identifiers, partitioning by that will result chunks with 1 row each, making the renumbering from 1 for each chunk a pretty much pointless operation while the old proofNr values were also 1.

Hmm... Wondering how efficient my above explanation was. ( Knowing my skills, probably not at all. ) Let us see in examples.

I used this test data :

test> select * from korrektur_copy;
+--------+---------+------------+-------------+----------------+------------+-------------+-----------+---------+
| ID     | auftrag | ausgang    | ausgang_von | ausgang_medium | eingang    | eingang_von | druckfrei | proofNr |
+--------+---------+------------+-------------+----------------+------------+-------------+-----------+---------+
| 187649 |  140802 | 2020-09-01 |          29 |              1 | 2020-09-08 |          29 | NULL      |       1 |
| 187650 |  140803 | 2020-09-01 |          29 |              1 | 2020-09-08 |          29 | NULL      |       1 |
| 187783 |  140802 | 2020-09-08 |          29 |              1 | 2020-10-08 |          29 | NULL      |       1 |
| 187784 |  140803 | 2020-09-08 |          29 |              1 | 2020-10-08 |          29 | NULL      |       1 |
| 188247 |  140802 | 2020-10-08 |          29 |              1 | 2020-10-19 |          29 | NULL      |       1 |
| 188248 |  140803 | 2020-10-08 |          29 |              1 | 2020-10-19 |          29 | NULL      |       1 |
| 188395 |  140802 | 2020-10-19 |          29 |              1 | NULL       |        NULL | NULL      |       1 |
| 188396 |  140803 | 2020-10-19 |          29 |              1 | NULL       |        NULL | NULL      |       1 |
+--------+---------+------------+-------------+----------------+------------+-------------+-----------+---------+
8 rows in set (0.00 sec)
 

My SQL statement with the partition by auftrag separates the data set into 2 chunks of 4 rows each...

test> select auftrag, count(*) from korrektur_copy group by auftrag;
+---------+----------+
| auftrag | count(*) |
+---------+----------+
|  140802 |        4 |
|  140803 |        4 |
+---------+----------+
2 rows in set (0.00 sec)
 

... and row_number()s each chunk from 1 to 4 :

test> select
    -> id, auftrag,
    -> row_number() over w proofnr_correct
    -> 
    -> from korrektur_copy
    -> 
    -> window w as (partition by auftrag);
+--------+---------+-----------------+
| id     | auftrag | proofnr_correct |
+--------+---------+-----------------+
| 187649 |  140802 |               1 |
| 187783 |  140802 |               2 |
| 188247 |  140802 |               3 |
| 188395 |  140802 |               4 |
| 187650 |  140803 |               1 |
| 187784 |  140803 |               2 |
| 188248 |  140803 |               3 |
| 188396 |  140803 |               4 |
+--------+---------+-----------------+
8 rows in set (0.00 sec)
 

Your SQL statement with the partition by id separates the data set into 8 chunks of 1 row each...

test> select id, count(*) from korrektur_copy group by id;
+--------+----------+
| id     | count(*) |
+--------+----------+
| 187649 |        1 |
| 187650 |        1 |
| 187783 |        1 |
| 187784 |        1 |
| 188247 |        1 |
| 188248 |        1 |
| 188395 |        1 |
| 188396 |        1 |
+--------+----------+
8 rows in set (0.00 sec)
 

... and row_number()s each chunk from 1 to 1 :

test> select
    -> id, auftrag,
    -> row_number() over w proofnr_correct
    -> 
    -> from korrektur_copy
    -> 
    -> window w as (partition by id);
+--------+---------+-----------------+
| id     | auftrag | proofnr_correct |
+--------+---------+-----------------+
| 187649 |  140802 |               1 |
| 187650 |  140803 |               1 |
| 187783 |  140802 |               1 |
| 187784 |  140803 |               1 |
| 188247 |  140802 |               1 |
| 188248 |  140803 |               1 |
| 188395 |  140802 |               1 |
| 188396 |  140803 |               1 |
+--------+---------+-----------------+
8 rows in set (0.01 sec)
 

Feherke.
feherke.github.io

RE: MySQL Query for incrementing field from duplicates in database

(OP)
Hi Feherke
you gave me the right idea und the understanding.

CODE --> MySQL

WITH cte AS (SELECT ID, ROW_NUMBER() OVER w proofNr_correct FROM korrektur_copy WINDOW w AS (PARTITION BY auftrag)) UPDATE korrektur_copy JOIN cte ON cte.ID = korrektur_copy.ID SET korrektur_copy.proofNr = cte.proofNr_correct WHERE auftrag = 140802; 
The change of the «PARTITION BY» keyword did the trick.

CODE --> MySQL

+--------+---------+------------+-------------+----------------+------------+-------------+-----------+---------+
| ID     | auftrag | ausgang    | ausgang_von | ausgang_medium | eingang    | eingang_von | druckfrei | proofNr |
+--------+---------+------------+-------------+----------------+------------+-------------+-----------+---------+
| 187649 |  140802 | 2020-09-01 |          29 |              1 | 2020-09-08 |          29 | NULL      |       1 |
| 187783 |  140802 | 2020-09-08 |          29 |              1 | 2020-10-08 |          29 | NULL      |       2 |
| 188247 |  140802 | 2020-10-08 |          29 |              1 | 2020-10-19 |          29 | NULL      |       3 |
| 188395 |  140802 | 2020-10-19 |          29 |              1 | NULL       |        NULL | NULL      |       4 |
+--------+---------+------------+-------------+----------------+------------+-------------+-----------+---------+ 

CODE --> MySQL

Query OK, 3 rows affected (0.93 sec)
Rows matched: 4  Changed: 3  Warnings: 0 
Thank you very much!
Greetings
kaeser

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close