0

I have a table "example"

 id | name | value
-------------------
 1  | 'ab' |   10
 2  | 'ab' |   30
 3  | 'ef' |   20
 1  | 'gh' |   40
 3  | 'kl' |   40
 1  | 'mn' |   40
 2  | 'mn' |   40

And I want to add once for every id1 a new row like this:

INSERT INTO example (id, name, value) VALUES (<every id>, 'ij', 50)

So that after that statement the table should look like this:

 id | name | value
-------------------
 1  | 'ab' |   10
 2  | 'ab' |   30
 3  | 'ef' |   20
 1  | 'gh' |   40
 3  | 'kl' |   40
 1  | 'mn' |   40
 2  | 'mn' |   40
 1  | 'ij' |   50
 2  | 'ij' |   50
 3  | 'ij' |   50

I know that I can get the Ids by 

SELECT DISTINCT id FROM example;

But I don't know how can merge both statements into one. So: How do I do this?

Marco Frost
  • 780
  • 3
  • 12
  • 25

1 Answers1

2

You can do it with insert as select and distinct, like this:

INSERT INTO example
(SELECT distinct ID,'ij',50 FROM example)

This is if those 3 columns are the only columns and in that order, if not , you need to specify (id, name, value) before the select.

The select will return in your case:

 1  | 'ij' |   50
 2  | 'ij' |   50
 3  | 'ij' |   50

And then will insert it

sagi
  • 40,026
  • 6
  • 59
  • 84