MorkaLork Development

Interesting stuff I've picked up over the years...

UPDATE examples

2009-04-16 21:21:52 | 234 views | sql mysql table update examples

In this sub article I will give several examples of how to use the UPDATE method. For example, it may not be obvious how to update several records at once, and so several example here might serve the purpose of clarifying the updateing concept.

The three main keywords when updating is UPDATE, SET and WHERE. The last one can be excluded, but it will cause all records to be updated.

We will use this table:

+--------------------+--------------------+--------------------+--------------------+
|store_name |store_sales |store_updates |store_location |
+____________________+____________________+____________________+____________________+
|Hanks Hardware 2500 2009-01-01 00:00:00 Stockholm |
|Santos SuperStore 750 2008-12-01 00:00:00 Oslo |
|Bobs Boutique 1050 2009-01-01 00:00:00 Stockholm |
|Pattyïs Pantstore 1050 2008-12-15 00:00:00 Stockholm |
|Hanks Hardware 2250 2008-01-01 00:00:00 Stockholm |
+-----------------------------------------------------------------------------------+



Updating multiple fields



Now, it turned out that our records are faulty. Bobs Boutique is not just located in Copenhagen(instead of Stockholm, as the database says), also, his store sales are 1250, not 1050. Pssh, these damn typ0s.

Well, we'll have to update two columns. Good thing this can be done in one query. The syntax for updating several columns(fields) in one query looks like this:

Syntax:

UPDATE table
SET change1, change2 etc
WHERE condition


Example:


UPDATE stores
SET store_sales=1250, store_location='Copenhagen'
WHERE store_name='Bobs Boutique'


+--------------------+--------------------+--------------------+--------------------+
|store_name |store_sales |store_updates |store_location |
+____________________+____________________+____________________+____________________+
|Hanks Hardware 2500 2009-01-01 00:00:00 Stockholm |
|Santos SuperStore 750 2008-12-01 00:00:00 Oslo |
|Bobs Boutique 1250 2009-01-01 00:00:00 Copenhagen |
|Pattyïs Pantstore 1050 2008-12-15 00:00:00 Stockholm |
|Hanks Hardware 2250 2008-01-01 00:00:00 Stockholm |
+-----------------------------------------------------------------------------------+



Update using equations



We can also use math when UPDATING. If we, for example, knows that in Stockholm, all store have to pay a special tax, say $200, we might just want to lower the sales for all stores in Stockholm by $200 in the column store_sales. This can be done like this:



UPDATE stores
SET store_sales=store_sales - 200
WHERE store_location='Stockholm'



+--------------------+--------------------+--------------------+--------------------+
|store_name |store_sales |store_updates |store_location |
+____________________+____________________+____________________+____________________+
|Hanks Hardware 2300 2009-01-01 00:00:00 Stockholm |
|Santos SuperStore 750 2008-12-01 00:00:00 Oslo |
|Bobs Boutique 1250 2009-01-01 00:00:00 Copenhagen |
|Pattyïs Pantstore 850 2008-12-15 00:00:00 Stockholm |
|Hanks Hardware 2050 2008-01-01 00:00:00 Stockholm |
+-----------------------------------------------------------------------------------+



Article comments

Feel free to comment this article using a facebook profile.

I'm using facebook accounts for identification since even akismet couldn't handle all the spam I receive every day.