SELECT DISTINCT values and INSERT INTO table

I want to take a column with values that repeat multiple times and get that value only once and store it for later use, but at the same time I would like to get another value in the same row as that distinct column.

A      B       C
32263  123456  44
32263  123456  45
32263  123456  46
32264  246802  44
32263  246802  45
32264  246802  46
32265  369258  44
32265  369258  45
32265  369258  46

A, B, C represent three columns. Ignore C for now.

My question is: How can I get this information in this table and store it for I can use it later in the script?

Here is what I tried:

use databaseName

select distinct A from tableName
order by A

The result is:

I’m trying to get it to also give me B’s value. (Note it does not matter at all which row I get since no matter what A I choose the value of B will be the same for given A.) We are ignoring C for now.

The result should be:

A      B
32263  123456
32264  246802
32265  369258

Now, once I get it like that I want to insert a row using the values I got from the query. This is where C comes in. I want to do something like this:

use databaseName

insert into tableName (A, B, C)
values (32263, 123456, 47)

Of course I don’t want to put the values directly inside of there, instead have some type of loop that will cycle through each of the 3 distinct A values I found.

In short, my table should go from:

A      B       C
32263  123456  44
32263  123456  45
32263  123456  46
32264  246802  44
32263  246802  45
32264  246802  46
32265  369258  44
32265  369258  45
32265  369258  46

To:

A      B       C
32263  123456  44
32263  123456  45
32263  123456  46
32263  123456  47 -
32264  246802  44
32263  246802  45
32264  246802  46
32264  246802  47 -
32265  369258  44
32265  369258  45
32265  369258  46
32265  369258  47 -

I placed dashes next to the newly added rows to help you see the changes.

I figure I should perhaps do some type of loop that will cycle through all three distinct A values, but my problem is how to do that?

Thanks for your time.

Answer

You can use INSERT INTO... SELECT statement on this,

INSERT INTO tableName (A, B, C)
SELECT A, B, MAX(C) + 1
FROM tableName
GROUP BY A, B

SQLFiddle Demo

Advertisement

Answer