CODEWITHSUNDEEP
mysqlsqldatabase
piface314
piface314

Reputation: 15

Finding distinct values in column and adding another column in sql

If I have a table with columns a and b as shown

a b
1 1
1 10
1 20
2 11
2 21
3 31

I want to insert into this table the distinct values in column a along with an arbitrary constant, let's say 0. So I want the output to be:

a b
1 1
1 10
1 20
2 11
2 21
3 31
1 0
2 0
3 0

How can I use INSERT INTO with DISTINCT to accomplish this? I'm not sure how to incorporate the arbitrary constant

Upvotes: 0

Views: 46

Answers (2)

Andrey Korneyev
Andrey Korneyev

Reputation: 26896

Pretty straightforward:

insert into your_Table (a, b)
select distinct a, 0
from your_Table

Upvotes: 0

jarlh
jarlh

Reputation: 44805

Simply do what you said, INSERT SELECT DISTINCT:

insert into tablename select distinct a, 0 from tablename

Upvotes: 1

Related Questions