How to use UPDATE with JOIN in SQL queries

[elizas]

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...
hope this would be helpful.

 

[cjard]

UPDATE BLOG_DETAILS SET IS_ACTIVE = 0 WHERE USER_ID IN (SELECT USER_ID FROM USER_DATA WHERE IS_ACTIVE = 0)

UPDATE BLOG_DETAILS BD SET IS_ACTIVE = 0 WHERE EXISTS (SELECT null FROM USER_DATA WHERE IS_ACTIVE = 0 AND USER_ID = BD.USER_OD)



But typically in a relational database system, we don't store this info in 2 places.. If we want to find out if a blog is active we link it using query and store the "activeness" of a blog in one place (the rule is "user inactive means blog inactive")

SELECT u.is_active, (other blog fields, other user fields) FROM user_data u INNER JOIN blog_data b ON b.user_id = u.user_id WHERE b.user_ID = ?