[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Simple MySQL IF statement troubles

Posted on 2009-04-20
4
Medium Priority
?
1,082 Views
Last Modified: 2012-05-06
I'm having some trouble using an IF statement in a simple query. I've not used SQL much so i'm not really sure of the syntax!

I basically want to say IF this data does not exist in the table, then insert this other data.

I tried something like this:

SET @existCount=0; SELECT @existCount := COUNT(*) FROM user_data WHERE username = 'Lexx87'; IF @existCount = 0 THEN INSERT into user_data values ('Lexx87', '1000Hz', 10)

Meaning if the count is at 0 (No username Lexx87 exists) then insert those values, but it doesn't seem happy with that!

I've taken some of that syntax from a different thread, so it may be out of date but I don't know!

Any help will be appreciated.
0
Comment
Question by:Lexx87
  • 2
  • 2
4 Comments
 
LVL 39

Expert Comment

by:Roger Baklund
ID: 24189715
You can not use IF like that in SQL.

Create a unique index (preferably a primary key) on the username column, then you can just do:

INSERT into user_data values ('Lexx87', '1000Hz', 10);

It will fail with a 'duplicate entry' error if the username allready exists. Just ignore the error, or make the server ignore it:

INSERT IGNORE into user_data values ('Lexx87', '1000Hz', 10);
0
 

Author Comment

by:Lexx87
ID: 24191781
But what if the username does exist?

My attempt was to just test the method, as more often than not the username and other values are going to exist. So I want to say that if those values already exist (all three are unique with the username a primary) then insert something else.

My table has fields username, frequency, decibel, frequency2, decibel 2.

frequency 2 and decibel2 are null to start off with, with the frst three fields filled in with data according to a users choice. If a user does the same operation a second time, I want to say that IF values username, frequency, decibel don't exist, INSERT into user_data values ('Lexx87', '1000Hz', 10);

ELSE IF they do exist

INSERT frequency2 and decibel2 where username=Lexx87

Am I making sense? :p My problem I think is that i'm thinking too much like Java for it, but different solutions for IF statements seem to follow different syntax in different places I look so i'm a little confused I guess.
0
 
LVL 39

Accepted Solution

by:
Roger Baklund earned 200 total points
ID: 24192405
>> But what if the username does exist?

Then it will not be inserted. The index prevents duplicates.

You can use a special syntax for insert: INSERT ... ON DUPLICATE KEY UPDATE

http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html

Example usage:
mysql> insert into user_data (username,frequency,decibel) values ('Lexx87','1000Hz',10) on
 duplicate key update frequency2='1000Hz',decibel2=10;
Query OK, 1 row affected (0.03 sec)
 
mysql> select * from user_data;
+----------+-----------+---------+------------+----------+
| username | frequency | decibel | frequency2 | decibel2 |
+----------+-----------+---------+------------+----------+
| Lexx87   | 1000Hz    |      10 | NULL       |     NULL |
+----------+-----------+---------+------------+----------+
1 row in set (0.00 sec)
 
mysql> insert into user_data (username,frequency,decibel) values ('Lexx87','1000Hz',10) on
 duplicate key update frequency2='1000Hz',decibel2=10;
Query OK, 2 rows affected (0.03 sec)
 
mysql> select * from user_data;
+----------+-----------+---------+------------+----------+
| username | frequency | decibel | frequency2 | decibel2 |
+----------+-----------+---------+------------+----------+
| Lexx87   | 1000Hz    |      10 | 1000Hz     |       10 |
+----------+-----------+---------+------------+----------+
1 row in set (0.00 sec)

Open in new window

0
 

Author Closing Comment

by:Lexx87
ID: 31572506
Thank you very much for the help :-) Works a treat! Sorry to provide something so basic but the only thing i've needed to do in SQL so far is nothing more SELECT * from table ;)

Very much appreciated.

Alex
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question