Tag: sql and my sql

MySQL INSERT IF NOT EXISTS: Checking whether a record exists and inserting

MySQL Insert If Not Exists

Why doesn’t MySQL Insert If Not exists work?????

UPDATE: CHECK AT THE BOTTOM OF POST

Bogdan has the answers .

First of all, this information relates solely to MySQL and may not apply to MSSQL or PostgreSQL.

Have you been trying to insert records into a table based on whether there is already a record there of not. Trying multiple SQL queries of the form:

INSERT INTO list
(code, name, place)
SELECT *
FROM
(SELECT ('ABC', 'abc ', 'Johannesburg',)) AS tmp
WHERE NOT EXISTS 
(
SELECT * FROM sharelist WHERE code = 'ABC'
);

Some people also use:

INSERT INTO table (fields) VALUES (values) ON DUPLICATE KEY UPDATE ID=ID;

Provided you have a unique primary key. This is not the correct way of using a Mysql Insert if not Exists…

Well Bogdan has the answers.

There is syntax used to tackle this exact problem in an efficient way and that is:


INSERT IGNORE INTO `list`
`code` = 'ABC',
`name` = 'abc',
`place` = 'Johannesburg',
;

It’s that easy.

If the record exists it will be ignored (silently skipped). If it does not it will be inserted.

Warm and Easy.

Update: No maybe not so easy. Damn you Mysql Insert if not Exists

The problem arises when you have a field (most likely an id) which is not added by you. So an auto incremented field. The above code will not skip the insert and will always insert the record in as it is unique because of the auto increment field.

So if you have an auto increment use the following:


INSERT INTO list (code, name, address,)
SELECT 'ABC', 'ABC name', 'Johannesburg'
FROM list
WHERE NOT EXISTS(
    SELECT code, name, address
    FROM list
    WHERE code = 'ABC'
	AND name = 'ABC name'
	AND address = 'Johannesburg'
)
LIMIT 1

That will do the job. However, one important point is that the table cannot be empty it must have at least one field so that is unfortunately a flaw.

Who thought a Mysql insert if not Exists would be this (easy) hard.

Read this question on stack: prevent autoincrement on MYSQL duplicate insert

Original article found on this blog: Mr.TimRosenblatt

If someone has a better idea please contact me: ME or add a comment.

Thanks
MySQL insert if not exists

MySQL Workbench and XAMPP setting up

This is a few pointers to use when setting up MySQL Workbench.

MySQL Workbench is a UI for using mySQL. It makes it easy to manage, create and modify your databases. It also gives tools to model a well structured database for any project you may have.

MySQL Workbench with XAMPP tutorial Setup

1. Start MySQL, either with XAMPP control panel or manually if you have installed it seperately (not as a stack)

2. Open MySQL Workbench, it is free and can be downloaded here: MySQL Workbench Download

3. You will be faced with this screen (Without any db’s added), click “New Connection” (Number ‘1’ on the image)

mysql-workbench-setup-xampp-tutorial

 

4. Add new connection, default settings will be localhost so name it “localhost” and fill in your password etc.

5. Then Click “New Server Instance” on the right (Number 2 on the image)

6. Click next next next everything should be OK.

7. If you try to connect it will fail, it’ll say something about WMI. So click “Manage Server Instance” (number 3 on the image), make sure you have the correct database selected (probably localhost). Next click “Manage Connections…”. Now Once the window has opened you do not need to do anything just close it.

8. Now you can double click “localhost” under “Server Administration” and you can administer your server. Then double click open connection to start querying on the left and you can start creating and querying tables in your database.

Happy trails.

More Info: