We use JOIN in the SELECT statement often to retrieve data from multiple related tables.
But, some times we can get into a situation where we may need to use UPDATE statement by joining multiple tables.
Consider a situation:
A table named as USER_DATA is there which contains user details. This table has one column(along with other columns) named as IS_ACTIVE which contain '0' for a user, when a user will be in-active.
One other table is there named as BLOG_DETAILS which contains columns as AUTHOR_ID, IS_ACTIVE and other column to keep blog details.
Now if we want to run a script which should make column IS_ACTIVE = 0 in the BLOG_DATA table for in-active users. Here, we need a sql script which will contain UPDATE with JOIN.
BLOG_DETAILS->AUTHOR_ID is foreign key which refers to the primary key USER_DATA->USER_ID
Let see how can we do it...
UPDATE BLOG_DETAILS SET IS_ACTIVE = 0 FROM BLOG_DETAILS BD INNER JOIN USER_DATA UD ON UD.USER_ID = BD.AUTHOR_ID AND UD.IS_ACTIVE = 0
and we are done...