CODEWITHSUNDEEP
mysqlsqlsql-insertcreate-table
raven_977
raven_977

Reputation: 475

MySQL create table if not exists and insert record only if table was created

I need to create a table and insert a first record only if the table was just newly created.

I do create the table with this statement:

CREATE TABLE IF NOT EXISTS tableName (
    id int(9) NOT NULL, 
    col1 int(9) DEFAULT NULL, 
    col2 int(3) unsigned zerofill DEFAULT NULL,
    PRIMARY KEY(id)
)  ENGINE = InnoDB DEFAULT CHARSET = latin1;

How do I insert an first record only if the table was just created?

Upvotes: 3

Views: 14210

Answers (2)

GMB
GMB

Reputation: 222582

That’s a good spot to use the INSERT IGNORE command rather than the INSERT command. 

INSERT IGNORE INTO mytable (id, field1, field2) VALUES(1, 'foo', 'bar');

From the mysql documentation :

Errors that occur while executing the INSERT statement are ignored. For example, without IGNORE, a row that duplicates an existing UNIQUE index or PRIMARY KEY value in the table causes a duplicate-key error and the statement is aborted. With IGNORE, the row is discarded and no error occurs. Ignored errors generate warnings instead.

Upvotes: 4

Barmar
Barmar

Reputation: 781731

Combine the creation and insert into a single statement:

CREATE TABLE IF NOT EXISTS tableName (
    id int(9) NOT NULL, 
    col1 int(9) DEFAULT NULL, 
    col2 int(3) unsigned zerofill DEFAULT NULL,
    PRIMARY KEY(id)
)  ENGINE = InnoDB DEFAULT CHARSET = latin1
AS SELECT 1 AS id, 10 AS col1, 5 AS col2;

If it doesn't create the table, AS SELECT ... clause is ignored.

Upvotes: 9

Related Questions