Month: March 2013

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?????


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:

(code, name, place)
(SELECT ('ABC', 'abc ', 'Johannesburg',)) AS tmp
SELECT * FROM sharelist WHERE code = 'ABC'

Some people also use:


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:

`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
    SELECT code, name, address
    FROM list
    WHERE code = 'ABC'
	AND name = 'ABC name'
	AND address = 'Johannesburg'

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.

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)



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:

Virtualhost and a XAMPP vhost “Error: Apache shutdown unexpectedly”

This is a problem I have experienced with XAMPP on a windows installation. It arises when editing the httpd-vhost.conf to create a virtualhost. Using Virtualhost is one of the various ways to host multiple websites or services from a single IP address. It comes in use when you are either hosting multiple hosts from 1 IP(internet Protocol) or developing on a machine with multiple web projects.

After installing apache, the virtual hosts configuration file can be found at: "C:/apache/conf/extra"

Virtualhost Format

The format of the httpd-vhosts.conf virtual host is as follows:

#remember to uncomment the below line

NameVirtualHost *:80

<VirtualHost *:80>
ServerAdmin //insert your email here
DocumentRoot "C:/xampp//your directory here"
ServerName //name (what you will put in addressbar)
SetEnv APPLICATION_ENV "development"xampp-control-panel-virtualhost
ServerAlias //any alias eg.
#ErrorLog "logs/logname.log"
#CustomLog "logs/logname.log" combined

<Directory "C:/xampp//your directory here">
DirectoryIndex index.php
AllowOverride All
Order allow, deny
Allow from All

So that is the basic structure when adding a new virtual host to your apache web server.

Here comes the pain

You can now go ahead and restart apache. However you will be hit with this message. Apache will fail to start when you click start.

Error: Apache shutdown unexpectedly.
12:29:35 PM [Apache] This may be due to a blocked port, missing dependencies,
12:29:35 PM [Apache] improper privileges, a crash, or a shutdown by another method.
12:29:35 PM [Apache] Check the "/xampp/apache/logs/error.log" file
12:29:35 PM [Apache] and the Windows Event Viewer for more clues

Oh well what now?

A warm and easy solution...

Add the following to your httpd-vhosts.conf file, after or before the previous :

<VirtualHost *:80>
DocumentRoot "C:\xampp\htdocs"  #Or your default document root
ServerName localhost

Now restart apache and it should work.

To be honest I'm not really sure why this happens, or what you can do if this does not work for you. If you have a better understanding do not hesitate to contact me or leave a comment.

You can read more on virtual hosting here: