CODEWITHSUNDEEP
mysqlsqldatabase
cynesis
cynesis

Reputation: 33

Updating multiple rows with first instance of record in same table

Assume following table:

+----+-----------+
| id | session   |
+----+-----------+
|  1 | abcd1234  |
|  2 | abcd1234  |
|  3 | abcd1234  |
|  4 | qwert5678 |
|  5 | qwert5678 |
|  6 | abcd1234  |
|  7 | abcd1234  |
|  8 | qwert5678 |
|  9 | abcd1234  |
| 10 | qwert5678 |
| 11 | qwert5678 |
| 12 | qwert5678 |
+----+-----------+

Suppose we want to get the first id of a given session, then set every instance of that session to the id for all sessions, such that the table becomes:

+----+-----------+
| id | session   |
+----+-----------+
|  1 |     1     |
|  2 |     1     |
|  3 |     1     |
|  4 |     4     |
|  5 |     4     |
|  6 |     1     |
|  7 |     1     |
|  8 |     4     |
|  9 |     1     |
| 10 |     4     |
| 11 |     4     |
| 12 |     4     |
+----+-----------+

We have a table with approximately 45M records, and are essentially changing every instance of column b to the value of min(column a) when grouped by column b.

Is there a way to do this in a single query? We have attempted several.

 update example e 
 set session = 
     (select id from 
         (select id,min(session) 
          from example as first_id
          group by session
         ) as this_id
 );

...which errors out: "Subquery returns more than 1 row".

 update example e 
 join 
     (select id 
      from 
          (select id,min(session) 
           from example as first_id 
           group by session
          ) as this_id
     ) as etable 
 set session = first_id;

...which errors out: "Unknown column 'first_id' in 'field list'". Also used 'this_id' to the same effect.

And other queries. Is this possible in a single query? Are we thinking about this incorrectly?

Upvotes: 2

Views: 815

Answers (1)

Justin
Justin

Reputation: 9724

Query:

SQLFIDDLEExample

UPDATE example
SET session =(SELECT MIN(e2.ID) 
              FROM (SELECT * 
                    FROM example) e2
              WHERE e2.session = example.session)

Result:

| ID | SESSION |
----------------
|  1 |       1 |
|  2 |       1 |
|  3 |       1 |
|  4 |       4 |
|  5 |       4 |
|  6 |       1 |
|  7 |       1 |
|  8 |       4 |
|  9 |       1 |
| 10 |       4 |
| 11 |       4 |
| 12 |       4 |

Upvotes: 2

Related Questions