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.
INSERT INTO table SELECT * FROM table
Moderator: General Moderators
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 table1So, values of fields 1 and 3 would become fixed, say 'blah' and 'blah-blah' respectively, regardless of what values they had in the table1?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.
If so, the query is simple:
Code: Select all
insert into table2
select
'blah' as field1,
field2,
'blah-blah' as field3,
field4,
field5
from table2yeah 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.
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.