Category: SQL

I am a true fan of mySQL and one of my favorite statements is the UPDATE statement. One of the big needs for any website with a wiki or with user generated content is the ability to edit content drawn from a database. The UPDATE statement is the statement that allows a webmaster to do this.

The example I will use here is a user table with columns for username, password, number of logins, and user description.

Say I want to update George’s user description – that can be done really easily using the UPDATE command in a mySQL database with proper WHERE clauses to ensure security.

There is one more little trick I want to share which I enjoy with the mySQL UPDATE statement and that is the ability to perform math on a column. Really beautifully simple to do and yet really useful in a whole host of cases… I hope you learn to love the mySQL UPDATE statement too!

So I don’t want to disappoint my friend who describes this site as everything geek… Earlier this week I was trying to work out an efficient way to find which cocktails in my cocktail making database had a certain set of ingredients and no others.

The solution to pulling this data turned out to be what is called a Left Outer Join and is supported in mySQL 5.0. The best way to describe what a left outer join does is an example.

What I could do is: SELECT Beauty.Name AS Name_b, Beauty.Sex AS Sex_b, Age.Name AS Name_a, Age.Sex AS Sex_a FROM Beauty LEFT OUTER JOIN Age ON Beauty.Name = Age.Name

Then I simply select those rows where the Name_a variable is null to get the filtered results. Job done!

mySQL does not support the “SELECT * FROM … INTO …” sybase SQL extension. That seemed a real bummer for me since I use that a lot in my work and wanted to use it while I was trying to repair my cocktail recipe database which was full of rubbish because I don’t check the input to the table.

After a bit of searching around I found that you could replicate this in two simple ways:

Using the CREATE … SELECT Method: This is a really simple way to fix the problem, if you have experience with the existing “SELECT” method you can use that to create a new table with exactly the same properties as the table you are selecting from.

In fact this method is really flexible since you can insert any column you wish into the new table.

This made my life a lot easier and I hope it makes your life easier too.