how can I create a new table selecting specific rows of the old table ?
The selected 3 rows have field1 = 243, 245 and 248 respectively.
Also I need to select the rows with field1 > 0 from table3.
thanks
+3
A:
You can use CREATE TABLE...AS SELECT... to make a table defined by the columns and data types of a query result set:
CREATE TABLE NewTable AS
SELECT * FROM Table3
WHERE field1 IN (243,245,248);
I can't tell what you mean about field1>0. I'll leave that to you.
Bill Karwin
2010-07-30 22:37:23
yeah, this is better actually, so +1. Leaving mine up though as well, as it also contains the `INSERT FROM SELECT` syntax he might be after.
Mike Sherov
2010-07-30 22:41:55
I meant field1 has a value > 0 i.e. a row with field1 = -20 is not selected
Patrick
2010-07-30 23:03:04
Yeah I know what > 0 means! :-) But I don't know which table you mean. Do you have two existing tables from which you want to take rows and stuff them into the new table? Because the values 243,245,248 are clearly already > 0.
Bill Karwin
2010-07-30 23:09:06
Yeah, ok. I wrote "table3" in the question, assuming "a different table". It is just another query on another table. They already gave me the answer by the way. Thanks.
Patrick
2010-07-31 08:06:33
A:
create table new_table like old_table;
insert into new_table select * from old_table where field1 in (243,245,248);
insert into new_table select * from table3 where field1 >0;
Mike Sherov
2010-07-30 22:38:14