Updating a table from a join

To understand this better let’s take a look at below contrived example. The opinions expressed here are my own and not of my employer.

For accuracy and official references refer to MSDN, Microsoft Tech Net, Books Online.

I or my employer do not endorse any of the tools / applications / books / concepts mentioned here on my blog.

I have simply documented my personal experiences on this blog.

This syntax is slightly more flexible as it means you can use left and right joins as well as inner joins, whereas the previous example is an implicit inner join.By default, UPDATE uses the WOS and if the WOS fills up, overflows to the ROS. (See Setting Search Paths.) You must be connected to the database that you specify.You cannot make changes to objects in other databases.Because of this indeterminacy, referencing other tables only within sub-selects is safer, though often harder to read and slower than using a join. [1] Set the value of column C1 in table T to 1, only in those rows where the value of column C2 is "a".CREATE TABLE `stock` ( `STOCK_ID` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, `STOCK_CODE` VARCHAR(10) NOT NULL, `STOCK_NAME` VARCHAR(20) NOT NULL, PRIMARY KEY (`STOCK_ID`) USING BTREE, UNIQUE KEY `UNI_STOCK_NAME` (`STOCK_NAME`), UNIQUE KEY `UNI_STOCK_ID` (`STOCK_CODE`) USING BTREE ) CREATE TABLE `category` ( `CATEGORY_ID` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, `NAME` VARCHAR(10) NOT NULL, `DESC` VARCHAR(255) NOT NULL, PRIMARY KEY (`CATEGORY_ID`) USING BTREE ) CREATE TABLE `stock_category` ( `STOCK_ID` INT(10) UNSIGNED NOT NULL, `CATEGORY_ID` INT(10) UNSIGNED NOT NULL, `CREATED_DATE` DATE NOT NULL, `CREATED_BY` VARCHAR(10) NOT NULL, PRIMARY KEY (`STOCK_ID`,`CATEGORY_ID`), CONSTRAINT `FK_CATEGORY_ID` FOREIGN KEY (`CATEGORY_ID`) REFERENCES `category` (`CATEGORY_ID`), CONSTRAINT `FK_STOCK_ID` FOREIGN KEY (`STOCK_ID`) REFERENCES `stock` (`STOCK_ID`) ) package com.mkyong.stock; import

Updating a table from a join