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
Go to Top of Page
   

- Advertisement -