CODEWITHSUNDEEP
mysqlsubquerysql-insert
Marco Frost
Marco Frost

Reputation: 800

Insert rows with value from another subquery

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?

Upvotes: 0

Views: 25

Answers (1)

sagi
sagi

Reputation: 40481

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

Upvotes: 2

Related Questions