CODEWITHSUNDEEP
sql
froadie
froadie

Reputation: 82993

Insert into from existing table?

I know there's probably a simple way to do this, but I can't seem to remember how.

Basically, I have a table with 2 fields, ID1 and ID2. I want to find all fields with an id2 = 1, and insert another record into the table with that id1 and a different id2.

So the query to select all the fields that I want a new record for is:

select id1 from mytable where id2 = 1

And then for each of those I want to insert:

insert into mytable(id1, id2) values([this id1], 6)

Upvotes: 2

Views: 400

Answers (3)

froadie
froadie

Reputation: 82993

Ok, I think I got it. Just had the syntax wrong. (Would delete the question, but can't)

insert into mytable (id1, id2)
select id1, 6 from mytable where id2= 1

(I was struggling with syntax like this:

insert into mytable (id1, id2) 
values((SELECT id1 FROM mytable WHERE id2 = 1), 6)

and couldn't figure out how to rearrange it)

Upvotes: 0

Bassam Mehanni
Bassam Mehanni

Reputation: 14944

Would this work for your purpose?

INSERT INTO mytable(id1, id2)
SELECT id, 6
FROM mytable
WHERE id2 = 1

Upvotes: 1

Cᴏʀʏ
Cᴏʀʏ

Reputation: 107498

You basically just have to combine the two queries you've already written in a slightly different way:

INSERT INTO mytable (id1, id2) 
SELECT id1, 6 FROM mytable WHERE id2 = 1

Upvotes: 3

Related Questions