SQL Update one table based on conditions in another table
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.
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')
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...