Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL - How to make a conditional INSERT

Tags:

sql

mysql

insert

Using only MySQL, I'm seeing if it's possible run an insert statement ONLY if the table is new. I successfully created a user variable to see if the table exists. The problem is that you can't use "WHERE" along with an insert statement. Any ideas on how to get this working?

// See if the "country" table exists -- saving the result to a variable
SELECT
    @table_exists := COUNT(*)
FROM
    information_schema.TABLES
WHERE
    TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'country';

// Create the table if it doesn't exist
CREATE TABLE IF NOT EXISTS country (
    id INT unsigned auto_increment primary key,
    name VARCHAR(64)
);

// Insert data into the table if @table_exists > 0
INSERT INTO country (name) VALUES ('Afghanistan'),('Aland Islands') WHERE 0 < @table_exists;
like image 311
Matt Avatar asked Nov 15 '08 15:11

Matt


People also ask

How do you add based on condition?

We have to provide appropriate condition in the WHERE clause to select specific rows. INSERT INTO table1 SELECT * FROM table2 WHERE condition; first_table: name of first table. second_table: name of second table.

Can we add if condition in SQL?

MySQL IF() FunctionThe IF() function returns a value if a condition is TRUE, or another value if a condition is FALSE.

How do you create a insert query?

There are two basic syntax of INSERT INTO statement is as follows: INSERT INTO TABLE_NAME (column1, column2, column3,... columnN)] VALUES (value1, value2, value3,... valueN);


2 Answers

IF @TableExists > 0 THEN
   BEGIN
       INSERT INTO country (name) VALUES ('Afghanistan'),('Aland Islands');
   END
like image 193
FlySwat Avatar answered Oct 14 '22 16:10

FlySwat


Use an if statement instead of the where clause:

http://dev.mysql.com/doc/refman/5.0/en/if-statement.html

like image 26
mmattax Avatar answered Oct 14 '22 15:10

mmattax