Update Columns Values From Another Table Column Values in MS SQL

Hi Guys,

Today i am going to tell you how to update column values from another table column values.


Lets Suppose I have two tables with the following structures. Now i want to update ItemTypeMaster table, the column supercategoryid is to be updated with values existing in the ItemCategoryMaster table's supercategory id column values. The Item_Cat_Id is Primary Key and CategoryId column in ItemTypeMaster table is Foreign Key.

ItemCategoryMaster Table
Item_Cat_Id    int,
Item_Cat_Desc    varchar(500),
Gender    varchar(1),
IsActive    bit,
SuperCategoryId    int,
Item_Cat_Picture    varchar(500)

ItemTypeMaster Table
ItemTypeId    int,
TypeName    varchar(50),
TypeDesc    varchar(500),
CategoryId    int,
SubCategoryId    int,
SuperCategoryId    int,
Gender    varchar(50),
ItemTypePicture    varchar(500),
IsActive    bit

Here is how to achieve the task.I was struggling to update my table column value based on a column value in another table. However i was surprised how the MS SQL update statements has got power of joins. We can use the joins in update query to update our column values.

The command i used to update the values is

update A set supercategoryid = B.supercategoryid
From ItemTypeMaster A
Inner Join itemcategorymaster B
On A.categoryid = B.item_cat_id

Similary, You can use select command to update the column values. For ex. I want to update IsActive column values based on the min id, The query would be:

update itemtypemaster set isactive=1 where itemtypeid in
(
select min(itemtypeid) from itemtypemaster
group by gender,supercategoryid,categoryid,subcategoryid,typename
)

Similarly, You can delete duplicate records from the table by using:

delete from itemtypemaster where itemtypeid not in
(
select min(itemtypeid) from itemtypemaster
group by gender,supercategoryid,categoryid,subcategoryid,typename
)

Hope this help guys!!

Thanks
Ritesh