CODEWITHSUNDEEP
sqlcreate-table
Grant
Grant

Reputation: 1

Create table from query results

I would very much appreciate an opinion about the best way to create a new table that will be a subset of an an existing table (based on the results of a query).

The new table is based on selecting only the specific records that contain any one of the 6 criteria (as shown below) for the first 2 characters of a selected field (field_name). All of the columns are required.

Here is my initial query draft. 

CREATE TABLE table_name_update
AS (SELECT *
     FROM table_name
     WHERE field_name LIKE ‘A7%
     OR A8%
     OR D0%
     OR D1%
     OR D3%
     OR E0%

Any comments or suggestions from the experts? Thanks much.

Upvotes: 0

Views: 326

Answers (1)

sgeddes
sgeddes

Reputation: 62861

This depends slightly on which RDBMS you are using, but one option is SELECT INTO:

SELECT * INTO table_name_update
FROM table_name 
WHERE field_name LIKE 'A7%' OR
    field_name LIKE 'A8%' OR 
    field_name LIKE 'D0%' OR 
    field_name LIKE 'D1%' OR 
    field_name LIKE 'D3%' OR 
    field_name LIKE 'E0%'

If using Oracle, use CREATE TABLE AS:

CREATE TABLE table_name_update AS
SELECT * INTO table_name_update
FROM table_name 
WHERE field_name LIKE 'A7%' OR
    field_name LIKE 'A8%' OR 
    field_name LIKE 'D0%' OR 
    field_name LIKE 'D1%' OR 
    field_name LIKE 'D3%' OR 
    field_name LIKE 'E0%'

Please notice, I've also updated your logic to use single quotes and multiple LIKE statements.

Upvotes: 1

Related Questions