SQL query for updating based on results

Question

I am trying to update records based on a secondary statement but i dont know how to link them together.

UPDATE WEBSITE SET CMS_ID = 99
SELECT * 
FROM website
WHERE is_scanned =  'yes'
AND cms_id =0

I want to update table website set the colum cms_id to 99 where all websites is_scanned = yes and cms_id = 0

will my query work?

score 2 · Accepted Answer · edited May 10 '13 at 00:02

2

This query doesn't work ?

UPDATE website SET cmd_id = 99
WHERE is_scanned = 'yes' AND cms_id = 0;

edited May 10 '13 at 00:02

Yang

answered May 09 '13 at 23:51

slig36

Indeed, it worked i must be tired, thanks for pointing that out – David Garcia May 09 '13 at 23:53

score 1 · Answer 2 · edited May 23 '17 at 12:13

1

I think this accomplishes the goal you stated and should work in most databases:

UPDATE WEBSITE SET CMS_ID = 99
WHERE is_scanned =  'yes'
AND cms_id =0

The SELECT FROM is not required unless joins between different records or tables are important to the goal of the update. Here are some other examples for SQL Server

edited May 23 '17 at 12:13

Community

answered May 10 '13 at 00:00

dewww

2 Answers2