When to web design and development, I’m all about 3 things: following best practices, creating usability, and writing efficient code. This post falls under the last category. To help you make your web application or sites faster and more efficient , I’m proving you with five advanced MySQL queries.
All five queries are single-line queries. The first four, however, are queries that could be split up and executed as two queries. Nevertheless, it’s much faster to run the single line query than to make two calls to the MySQL server from your PHP (or another server-side script) code.
Insert or Update In A Single Query
There are times when you need to insert data into a table, but if the data is already there, you want to update it with the provided values. As I mentioned above, you could run one query to check for a duplicate entry and then run a second query to either update or insert based on the result. Alternatively, you can run the advanced, single-line query I’m about to show.
Let’s beging with a simple Movies table that already has one entry on it, as shown below. The ID column is the primary key and title has to be a unique key for this query to work:
movies id title year category
A Nightmare on Elm Street 1984 Scary
We now want to insert a row with “A Nightmare on Elm Street ” as the movie title and “Horror” as the category. If the movie “A Nightmare on Elm Street” is already in the table, we simply want to update the category. The query to accomplish this task looks like this:
INSERT INTO movies (title, category) VALUES (‘A Nightmare on Elm Street’, ‘Horror’) ON DUPLICATE KEY UPDATE category=’Horror’;
Insert If Row Not Already There
The common way to insert a row or do nothing if the row is already there is to use the IGNORE keyword like this:
INSERT IGNORE INTO movies (title) VALUES (‘A Nightmare on Elm Street’);
However, let’s say we want to insert “A Nightmare on Elm Street” with year 2010. Now it requires that you check that both the title and the year already exist. In two queries it’s easy to do, but how would you accomplish this in a single query? The trick is to use the special DUAL table. The keyword references a non-existent table used to satisfy the syntax.
For this example, we’re using the same table schema as the example above. However, the title field is no longer a unique key, since we can have two movies of the same name as long as they are not of the same year. The query looks like this:
INSERT INTO movies (title, year) SELECT “A Nightmare on Elm Street”,”2008″ FROM DUAL WHERE NOT EXISTS (SELECT title FROM movies WHERE title=”A Nightmare on Elm Street” AND year=”2010″);
Whoa! That looks intimidating, but it’s really not that bad. You start the query in the traditional way. Then, however, you use the SELECT … FROM DUAL syntax to select the values that you want to insert. These values will only insert if the next SELECT query in parenthesis returns an empty result.