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;
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.
MySQL IF() FunctionThe IF() function returns a value if a condition is TRUE, or another value if a condition is FALSE.
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);
IF @TableExists > 0 THEN
BEGIN
INSERT INTO country (name) VALUES ('Afghanistan'),('Aland Islands');
END
Use an if statement instead of the where clause:
http://dev.mysql.com/doc/refman/5.0/en/if-statement.html
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With