Search Marketing

   
   

Update Multiple Tables Using One Query

Thu Jan 06, 2011 5:36 am
<<     >>
Comments: 2 Views: 24707

Updating a MySQL Database table is similar to Selecting from one, or two as we are now dealing with more than one table.

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

1. Lets assume we have the following database tables.

mysql> select * from Name;
+----+--------+---------------------+---------------+
| ID | Name | Date | Hometown |
+----+--------+---------------------+---------------+
| 1 | Joe | 2007-10-29 12:47:10 | NY |
| 2 | Jack | 2000-01-01 00:00:00 | San Francisco |
| 3 | John | 2000-03-21 00:00:00 | 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 |
+----+--------+---------------------+---------------+

mysql> select * from Work;
+----+----------+--------+---------------------+
| ID | Location | NameID | Year |
+----+----------+--------+---------------------+
| 1 | NY | 3 | 2007-10-29 15:38:34 |
| 2 | LA | 2 | 2007-10-29 15:38:40 |

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

2. Lets assume we wanted to update the Hometown of anyone in the "Name" table whose Date has the year 2000, to whatever the Location is in the "Work" table AND update the Year field in the table "Work" to use whatever the Date is in the "Name" table.

Note: Basically we are just using the information in each table to update the other using an "INNER JOIN".

mysql> update Name n, Work w set n.Hometown=w.Location,w.Year=n.Date where left(n.Date,4)='2000' && n.ID=w.NameID;

mysql> select * from Name;
+----+--------+---------------------+----------+
| ID | Name | Date | Hometown |
+----+--------+---------------------+----------+
| 1 | Joe | 2007-10-29 12:47:10 | NY |
| 2 | Jack | 2007-10-29 15:38:34 | LA |
| 3 | John | 2007-10-29
15:38:40 | NY |
| 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 |
+----+--------+---------------------+----------+

mysql> select * from Work;
+----+----------+--------+---------------------+
| ID | Location | NameID | Year |
+----+----------+--------+---------------------+
| 1 | NY | 3 | 2000-01-01 00:00:00 |
| 2 | LA | 2 | 2000-03-21 00:00:00 |

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

 



Comments
Mon May 04, 2009 8:07 am
Name: mesjojo | Comment: mysql> select * from Work;
+----+----------+--------+---------------------+
| ID | Location | NameID | Year |
+----+----------+--------+---------------------+
| 1 | NY | 3 | 2000-01-01 00:00:00 |
| 2 | LA | 2 | 2000-03-21 00:00:00 |
+----+----------+--------+---------------------+
I think it's better to use relational db system

Thu Jan 06, 2011 5:36 am
Name: | Comment:

Name:
*
URL: http: (ex. cnn.com)
Comment:
*
Number:
Math (19 + 4)
* required
SubCategories
Books

© 2017 Christonium LLC

Christonium.com
|
Terms of Use
|
Privacy
ccc