SELECT DISTINCT values and INSERT INTO table

Question

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.

There's an errant 32263 in the middle of the 32264 section. — Hart CO
– Hart CO, Commented Sep 25, 2013 at 20:13

John Woo · Accepted Answer · 2013-09-25 20:15:19Z

16

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

edited Sep 25, 2013 at 20:15

answered Sep 25, 2013 at 20:06

John Woo

265k70 gold badges509 silver badges500 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

cdsaenz Over a year ago

Cool. Opened my eyes to import from a populated table over to a codes table: INSERT INTO code_mst (code_value,code_field) SELECT DISTINCT app_brand,'app_brand' FROM app_mst

ItsPete · Accepted Answer · 2021-09-24 03:09:23Z

1

Try this:

SELECT * INTO new_table FROM
   (SELECT DISTINCT * FROM table_name) x

edited Sep 24, 2021 at 3:09

ItsPete

2,3694 gold badges28 silver badges37 bronze badges

answered Mar 4, 2017 at 5:28

sabihul haque

111 bronze badge

Comments

Khan · Accepted Answer · 2013-09-25 20:07:36Z

0

If I understand what you're trying to accomplish correctly, you should be able to do this:

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

This should insert a row for every distinct combination of A and B as well as increment C.

answered Sep 25, 2013 at 20:07

Khan

18.2k5 gold badges52 silver badges60 bronze badges

Comments

Vulcronos · Accepted Answer · 2013-09-25 20:13:30Z

0

This can be done using windowing functions.

INSERT INTO TABLENAME (A, B, C)
SELECT A, B, C + 1
FROM(
select A, B, C, MAX(C) OVER(PARTITION BY A, B) AS MAXC
FROM TABLENAME 
) AS NEW_ROWS
WHERE C = MAXC

edited Sep 25, 2013 at 20:13

answered Sep 25, 2013 at 20:07

Vulcronos

3,4563 gold badges19 silver badges25 bronze badges

2 Comments

Hart CO Over a year ago

This would add many duplicates, OP just wants 1 row per grouping.

Vulcronos Over a year ago

@GoatCO Updated my query to filter. A group by will work most of the time until you just want to group by A. Just wanted to introduce windowing function since they are very useful.

Collectives™ on Stack Overflow

SELECT DISTINCT values and INSERT INTO table

4 Answers 4

1 Comment

Comments

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related