MySQL Joins Update Query Tutorial
Sometimes updating information in a table requires information in a different table. MySQL lets you update the data in one table based on matching information in another table by using both the "update" and "join" clauses in one query. You can join as many tables as you need as long as the database contains at least two tables. For example, you can use a table that contains a list of airplane passengers to update a table that keeps track of occupied plane seats.
Other People Are Reading
Instructions
-
-
1
Open MySQL, connect to the database and open the command line interface.
-
2
Type "UPDATE plane JOIN passengers ON plane.seat = passengers.seat SET plane.occupied = 'Yes' WHERE passengers.name = 'Jim';" into the command line.
-
-
3
Press the "Enter" key to execute the query. MySQL will find all records in the passengers table that match "Jim" in the name column. It then locates all records in the plane table that have matching data in both tables' "seat" fields. MySQL then updates the records in the plane table to show that the seats Jim owns are now occupied. For example, if Jim buys seats 10 and 11 in the passengers table, this query will change the records for seats 10 and 11 in the plane table to reflect that those two sears are now occupied.
-
4
Type "SELECT * FROM table1" and press "Enter" to execute the query. Examine the results.
-
1
Tips & Warnings
Using a "join update" can help you update a table quickly. In this example, Jim can buy as many seats as he wants and you would only need to execute the "update" query on the plane table once to reflect that each seat is now occupied, instead of one time for each seat in the table.
