UPDATE examples

Written by: maffelu , 2009-04-16 21:21:52

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           |

+-----------------------------------------------------------------------------------+

There are no comments on this article.

Leave a message!

If you have any question or just want to leave a message, just fill out the form below!

Your e-mail will not be visible in your post, it is for validation reasons only

Name:
E-mail:
Comment: