Alex Schultz: APIs

mySQL UPDATE statement working example

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.

In the majority of uses it is composed of 3 main parts (but of course can do a lot more):

UPDATE tablename SET column='value' WHERE column='value'

The example I will user here is a user table which for the sake of argument I shall call tbl_user and will have four simple columns. One with their username (A_STR_username), one with their password (A_STR_password), one with their number of logins (A_INT_usertotallogins) and finally one with their user description (A_BLOB_userdescription)

A_STR_username	A_STR_password	A_INT_usertotallogins	A_BLOB_userdescription
George	r3db0y	2	UPDATE SET SELECT INTO WHERE AND SET UPDATE WHERE UPDATE SELECT INTO AND
Patrick	fr33d0m	27	UPDATE SET SELECT INTO WHERE AND SET UPDATE WHERE UPDATE SELECT INTO AND
Jimmy	cr3l4t	4	UPDATE SET SELECT INTO WHERE AND SET UPDATE WHERE UPDATE SELECT INTO AND
Jane	l4gun4	15	UPDATE SET SELECT INTO WHERE AND SET UPDATE WHERE UPDATE SELECT INTO AND

 Now say I want to update George's user description that can be done really easily using the UPDATE command in a mySQL database. I would want to ensure that I only upate George if he has given me the right password though so...

UPDATE tbl_users SET A_BLOB_userdescription = 'I love mySQL UPDATE statements!' WHERE A_STR_username='George' AND A_STR_password = 'r3db0y'

This query will only update a row where the username is George and the password is redboy and otherwise will throw and error. This is awesome in my opinion.

 

A_STR_username	A_STR_password	A_INT_usertotallogins	A_BLOB_userdescription
George	r3db0y	2	I love mySQL UPDATE statements!
Patrick	fr33d0m	27	UPDATE SET SELECT INTO WHERE AND SET UPDATE WHERE UPDATE SELECT INTO AND
Jimmy	cr3l4t	4	UPDATE SET SELECT INTO WHERE AND SET UPDATE WHERE UPDATE SELECT INTO AND
Jane	l4gun4	15	UPDATE SET SELECT INTO WHERE AND SET UPDATE WHERE UPDATE SELECT INTO AND

 

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. I will do a simple addition but there is loads more you can do. A use case for this example could be incrementing the number of time Jimmy has logged into your site

UPDATE tbl_users SET A_INT_usertotallogins = A_INT_usertotallogins+1 WHERE A_STR_username='Jimmy' AND A_STR_password = 'cr3l4t'

The result of this change will be just to increment the log total logins column of the table (as you see below). 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 (and of course it works in more than just mySQL).

A_STR_username	A_STR_password	A_INT_usertotallogins	A_BLOB_userdescription
George	r3db0y	2	I love mySQL UPDATE statements!
Patrick	fr33d0m	27	UPDATE SET SELECT INTO WHERE AND SET UPDATE WHERE UPDATE SELECT INTO AND
Jimmy	cr3l4t	5	UPDATE SET SELECT INTO WHERE AND SET UPDATE WHERE UPDATE SELECT INTO AND
Jane	l4gun4	15	UPDATE SET SELECT INTO WHERE AND SET UPDATE WHERE UPDATE SELECT INTO AND

mySQL Left Outer Join

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. i.e. tell me what you have in your bar and I will tell you what drinks can be made with a subset of those ingredients.

The solution to pulling this data turned out to be what is called a Left Outer Join and is supported in mySQL 5.0 (and possibly below this but I don't have a lower version to check).

The best way to describe what a left outer join does is an example so let's name two tables: age and beauty (the names of the trial eights in my college boatclub when I was captain).

Age Name Sex Brad Pitt Male Mick Jagger Male Jen Anniston Female Mo Mowlam Female

Beauty Name Sex Brad Pitt Male Josh Hartnett Male Jen Anniston Female Shakira Female

I may well want to find those people who are beautiful but not old and if would be great to have an effective way to do this without doing some complex IS NOT IN statement.

So what I could do is as follows:

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

Which would give me the following result:

Name_b	Sex_b	Name_a	Sex_a
Brad Pitt	Male	Brad Pitt	Male
Josh Hartnett	Male	Null	Null
Jen Anniston	Female	Jen Anniston	Female
Shakira	Female	Null	Null

However I would like to just see young beautiful people and so I want to get rid of Brad and Jen (too old). In order to do that I simply select those rows where the Name_a variable is null.

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 WHERE Name_a IS NULL

Job now done the output is as follows and I have gone from a list of old people and a list of beautiful people of all ages to a list which just contains young beautiful people... now off to Google!

Name_b	Sex_b	Name_a	Sex_a
Josh Hartnett	Male	Null	Null
Shakira	Female	Null	Null

Working Around SELECT INTO for mySQL

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 outlined below:

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. An example statement below would copy exactly the table tbl_Ingredients from a cocktail database across to the placement tbl_Ingredients_temp.

CREATE tbl_Ingredients_temp SELECT * FROM tbl_Ingredients

In fact this method is really flexible since you can insert any column you wish into the new table. For example if you wanted to add a new column which allowed you to announce if an ingredient had been added after the new version you could (the values in that column will all be NULL for exising ingredients).

CREATE tbl_Ingredients_temp (A_INT_newversion INT) SELECT * FROM tbl_Ingredients

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