Search Marketing

   
   

Update Database Table Query Statement

Tue Feb 02, 2010 12:55 am
<<     >>
Comments: 0 Views: 2407

Updating a MySQL Database table is similar to Selecting from one.

You use the "Where" option in the statement to determine which entries (rows) become changed.

1. Lets assume we have the following database table.

mysql> select * from Name;
+----+--------+---------------------+---------------+
| ID | Name | Date | Hometown |
+----+--------+---------------------+---------------+
| 1 | Joe | 2007-10-29 12:47:10 | NY |
| 2 | Jack | 2007-10-29 12:50:36 | San Francisco |
| 3 | John | 2007-10-29 12:50:36 | New York |
| 4 | James | 2007-10-29 12:47:34 | Paris |
| 5 | George | 2007-10-29 12:47:40 | Berlin |
| 6 | Paul | 2007-10-29 12:47:48 | Prague |
| 7 | Simon | 2007-10-29 12:47:55 | London |
| 8 | Jim | 2007-10-29 12:48:01 | Paris |
+----+--------+---------------------+---------------+

The table above has four fields (ID,Name,Date and Hometown).

2. Lets assume we wanted to update the Hometown of Paul from Prague to Moscow.

mysql> update Name set Hometown='Moscow' where ID=6;
Query OK, 1 row affected (0.07 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from Name;
+----+--------+---------------------+---------------+
| ID | Name | Date | Hometown |
+----+--------+---------------------+---------------+
| 1 | Joe | 2007-10-29 12:47:10 | NY |
| 2 | Jack | 2007-10-29 12:50:36 | San Francisco |
| 3 | John | 2007-10-29 12:50:36 | New York |
| 4 | James | 2007-10-29 12:47:34 | Paris |
| 5 | George | 2007-10-29 12:47:40 | Berlin |
| 6 | Paul | 2007-10-29 13:53:04 | Moscow |
| 7 | Simon | 2007-10-29 12:47:55 | London |
| 8 | Jim | 2007-10-29 12:48:01 | Paris |
+----+--------+---------------------+---------------+

You can see that two fields actually changed in the row we wanted changed. This is an example of something inadvertaintly happening becuase of the way the database table was created. We expected to see Moscow, but if you look further, the date also changed.

This error was caused by having our field specification set to TIMESTAMP. If we did not want the date to change by way of an update, then we should have set the field characteristic to DATE.

Note: You could also set Date=Date when you perform the update...as in

update Name set Hometown='Moscow',Date=Date where ID=6;



Comments
Name:
*
URL: http: (ex. cnn.com)
Comment:
*
Number:
Math (10 + 3)
* required
SubCategories
Books

© 2017 Christonium LLC

Christonium.com
|
Terms of Use
|
Privacy
ccc