|
Custom Search
| |
|
Use outer join in update In this tutorial, we're going to look at how to use outer joins in UPDATE statement. First, to run the queries on this page, we need to create a new table in our popular MySQL Northwind database. Create table - Categories_Test CREATE TABLE `categories_test` Insert some data: -- Data for the table `categories_test` Here is the data view after the table is populated.
Our task - change data in the Picture column for records which are not in the Categories table The Categories_Test table contains two more categories - Herbs/Spices and Vegetables. As they are new categories, we'd like to add a prefix to the filename of their pictures so that we can be easily identified as new categories. The prefix we're going to add is 'new-' (without the single quotes) so the picture names will be changed to below.
Look at the data in table Categories shown below. It's obvious that Categories_Test has two 2 records - CategoryID 9 and 10.
The most common way of adding the prefix is to do a simple update in table Categories_Test for those records whose CategoryID cannot be found in Categories table. update categories_test The alternative way of adding the prefix is an update with left outer join to find the new categories. As it uses left join, all the records from the table in the left hand side of the JOIN clause (that is the Categories_Test table) are returned in the result. All the matching rows in the right hand side of the JOIN clause (that is the Categories table) are also returned in the result. For the unmatched rows, the data is returned as NULL values in Categories table. If this does not seem to be straight forward for you, please refer to Using Outer Joins to Combine Data from Two Tables for detailed explanation about using outer joins in SELECT statement. Here is what the result would look like for the left join before an update is run.
Here is the update query by using left join. We can use b.categoryid is null in the WHERE clause to identify those unmatched rows for new categories that don't exist in Categories table. update categories_test as a
2 records updated:
Happy Coding!
Other tutorials in this category 1. Update Statement in MySQL |
Copyright © 2024 GeeksEngine.com. All Rights Reserved. This website is hosted by HostGator. No portion may be reproduced without my written permission. Software and hardware names mentioned on this site are registered trademarks of their respective companies. Should any right be infringed, it is totally unintentional. Drop me an email and I will promptly and gladly rectify it. |
Home | Feedback | Terms of Use | Privacy Policy |