Update statement with joins - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Update statement with joins

Author

Topic

Mondeo
Constraint Violating Yak Guru

287 Posts

Posted - 2014-07-18 : 05:17:06

I've got this select statement

SELECT Replies.*, JobDetails.*, Subscribers.*
FROM tblSmsReplies Replies
LEFT JOIN tblJobDetails JobDetails ON Replies.JobDetailId = JobDetails.JobDetailId
LEFT JOIN tblSubscribers Subscribers ON JobDetails.SubscriberId = Subscribers.SubscriberId
WHERE Replies.SmsContent = 'stop'

How can I make that an update statement, to update all those rows to Subscribers.StatusId = 2

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-07-18 : 05:27:43

[code]-- Select
SELECT dbo.Replies.*,
dbo.JobDetails.*,
dbo.Subscribers.*
FROM dbo.tblSmsReplies AS Replies
LEFT JOIN dbo.tblJobDetails AS JobDetails ON JobDetails.JobDetailId = Replies.JobDetailId
LEFT JOIN dbo.tblSubscribers AS Subscribers ON Subscribers.SubscriberId = JobDetails.SubscriberId
WHERE Replies.SmsContent = 'stop';

-- Update
UPDATE Subscribers
SET Subscribers.StatusId = 2
FROM dbo.tblSmsReplies AS Replies
LEFT JOIN dbo.tblJobDetails AS JobDetails ON JobDetails.JobDetailId = Replies.JobDetailId
LEFT JOIN dbo.tblSubscribers AS Subscribers ON Subscribers.SubscriberId = JobDetails.SubscriberId
WHERE Replies.SmsContent = 'stop';[/code]

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources