INSERT INTO table SELECT * FROM table

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
irealms
Forum Contributor
Posts: 215
Joined: Mon Apr 28, 2003 7:10 am
Location: Leeds

INSERT INTO table SELECT * FROM table

Post by irealms »

I want to run a query in the form

INSERT INTO table SELECT * FROM table

which i can do with no problems.

The problem i do i have is the data pulled in the select area needs to bbe changed slightly before inserting.

I Want to pull out the info, change 2 entries and then insert into another table. Is there a way to do this without building a table of results and then reinserting each one in the form INSERT INTO table (field1,field2,field3) VALUES (entry1,entry2,entry3) ?

If i did it this way then the query would have to be changed each time the table structure changed.
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post by Weirdan »

hmm... perhaps something like this:

Code: Select all

insert into table2
select 
  case id 
     when 1 then 'something'
     when 2 then 'something_else'
  end case as field1,
  case id
     when 2 then 'blah'
     when 10 then 'blah-blah-blah'
  end case as field 2,
  field3 /* as is */
from table1
User avatar
irealms
Forum Contributor
Posts: 215
Joined: Mon Apr 28, 2003 7:10 am
Location: Leeds

Post by irealms »

Hmm not sure i follow that.

Say i had this

INSERT INTO table2 SELECT * FROM table1

table 1 has say 5 fields, and i want to change field 1 and field 3 and insert all 5 fields with 1 and 3 changed.

So it would insert fields 2,4,5 as they were in table1, but 1 and 3 altered.
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post by Weirdan »

irealms wrote:Hmm not sure i follow that.

Say i had this

INSERT INTO table2 SELECT * FROM table1

table 1 has say 5 fields, and i want to change field 1 and field 3 and insert all 5 fields with 1 and 3 changed.

So it would insert fields 2,4,5 as they were in table1, but 1 and 3 altered.
So, values of fields 1 and 3 would become fixed, say 'blah' and 'blah-blah' respectively, regardless of what values they had in the table1?
If so, the query is simple:

Code: Select all

insert into table2
select
  'blah' as field1,
   field2,
   'blah-blah' as field3,
   field4,
   field5
from table2
User avatar
irealms
Forum Contributor
Posts: 215
Joined: Mon Apr 28, 2003 7:10 am
Location: Leeds

Post by irealms »

yeah so 1 and 3 could work like this $variable as field1 ?

That will work fine, also is there a way to get it to detect fields automatically, so it knows what fields to put in rather than me doing blas as field1, nextname?

So all i enter is the ones that need changing and it draws the rest? This would save updating in future if bits need adding to the table.
Post Reply