mysql query - insert output of a statement as records into a new table?

Question

i've got this table called items and there's a blank table called expensive items, what code do i write to insert all items in ITEMS which cost more than 50 into EXPENSIVEITEMS table?

Answer 1

insert into EXPENSIVEITEMS
select * from ITEMS where cost > 50;

This of course assumes that ITEMS and EXPENSIVEITEMS have the same structure. But why would you want to keep a table of expensive items when they already exist in items and you can find them with where cost > 50?

Answer 2

I agree with the previous answer, why would you want to create a separate table to store the same data you've already got? It sort of defeats the purpose of having a databse and querying it in the first place because your new table isn't going to be kept automatically up to date when new items are inserted into the ITEMS table. If you really need to do this, you'd be better off writing a View. A View is basically just a query from other tables that can be accessed as if it was it's own table, this means it's dynamic and constantly updated with whatever data is in the table(s) it's based on. More about Views here:

http://www.fluffycat.com/SQL/Views/

To be honest thought it seems unlikely you'd even need that, whenever you need to get a list of these 'expensive items' you'd just use a query to select only those with a certain price from your ITEMS table.

Answer 3

BigRez answered the question as asked, but Bamba has the correct way of doing this.

Data should never be duplicated