CODEWITHSUNDEEP
mysqlsql
user3662671
user3662671

Reputation: 3

Nested SQL Query and Update

I'm sure it's simple, but I'm having a hard time coming up with the right update/select statement.

I have a table called POSTS. The table has POSTID which is PK. It has PARENTID which is currently all NULL. It also has GROUPID which has a legacy numerical value that groups POSTS together.

An example of the GROUPID, if I have three posts that make up a single thread, they would all have the same value in GROUPID.

What I'm trying to do is to loop through each record, make a note of the POSTID and GROUPID, select all other records that have the same GROUPID and update their PARENTID with the POSTID I noted from the first search.

| POSTID | PARENTID | GROUPID |
| 109    | NULL     | 20      |
| 110    | NULL     | 20      |
| 111    | NULL     | 20      |
| 112    | NULL     | 21      |
| 113    | NULL     | 21      |

I want to make it as follows:

| POSTID | PARENTID | GROUPID |
| 109    | NULL     | 20      |
| 110    | 109      | 20      |
| 111    | 109      | 20      |
| 112    | NULL     | 21      |
| 113    | 112      | 21      |

I hope it makes sense.

Thanks!

Upvotes: 0

Views: 94

Answers (1)

sgeddes
sgeddes

Reputation: 62861

Assuming I'm understanding your question correctly, you can join the table back to itself using the min aggregate. Then you can use case to determine if the min post id is the parent or not.

Here is one way to do this:

select t.postid,
  case when postid <> minpostid then minpostid end parentid,
  t.groupid
from yourtable t
  join (select groupid, min(postid) minpostid 
    from yourtable t2 
    group by groupid) t2 on t.groupid = t2.groupid

If you want to update the data in your table, you can use the same query with a join:

update yourtable t
  join (select groupid, min(postid) minpostid 
    from yourtable t2 
    group by groupid) t2 on t.groupid = t2.groupid
set t.parentid = case when postid <> minpostid then minpostid end;

Upvotes: 2

Related Questions