SQL Update one table based on conditions in another table

Question

Two tables.

Content (table),
   topic_id (primary key),
   data (text)

Topics (table),
   topic_id (primary key),
   content_type (text)

Both tables have the same primary key data (topic_id).

I need to update the data field (Content table) with the text "disabled" but only where the content_type field (Topics table) = the text "rvf"

I can: SELECT * from topics WHERE content_type = "rvf";

I can: UPDATE content SET data = ("disabled");

But how can I put those together.

score 42 · Accepted Answer · answered Apr 15 '11 at 22:29

42

Standard ANSI SQL solution (should work on any DBMS)

UPDATE content 
   SET data = 'disabled'
 WHERE topic_id IN (SELECT t.topic_id 
                    FROM topics t
                    WHERE t.content_type = 'rvf')

answered Apr 15 '11 at 22:29

Thank you, worked perfectly. This was in SQlite, forgot to mention that. – Josh Bond Apr 15 '11 at 22:48

score 13 · Answer 2 · answered Apr 15 '11 at 22:29

This should work if you are using SQL Server

UPDATE content 
SET data = 'disabled'
FROM content
INNER JOIN topics
on content.topic_id = topics.topic_id
WHERE content_type = 'rvf'

You can also update content with a value from topics by doing something like this:

UPDATE content 
SET content.data = topics.content_type
FROM content
INNER JOIN topics
on content.topic_id = topics.topic_id
WHERE content_type = 'rvf'

Not sure if it applies in this case, but it's good to know you can...

Isn't that missing the condition for `content_type = 'rvf'` – Apr 15 '11 at 22:30 — , Apr 15 '11 at 22:30

SQL Update one table based on conditions in another table

2 Answers2