SQL 2012 : Replace SELECT with UPDATE in stored procedure

PortletPaul kindly helped me create a neat SELECT query.

I now need to change that select query to an update query so that the newly created stopdates and startdates are input to tbl_MachineData......

The Select query is below

SELECT * FROM dbo.tbl_MachineData
OUTER APPLY (SELECT

(SELECT TOP (1) Date
FROM dbo.tbl_MachineData t2
WHERE t2.RunStatus <> dbo.tbl_MachineData.RunStatus
AND t2.RunStatus = 'TRUE'
AND t2.Date < dbo.tbl_MachineData.Date
ORDER BY t2.Date DESC),

(SELECT TOP (1) Date
FROM dbo.tbl_MachineData t2
WHERE t2.RunStatus <> dbo.tbl_MachineData.RunStatus
AND t2.RunStatus = 'TRUE' AND t2.Date > dbo.tbl_MachineData.Date
ORDER BY t2.Date) ) oa2 (StartDate, StopDate)

Thanks in advance
SweetingAAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott PletcherSenior DBACommented:
UPDATE dbo.tbl_MachineData
SET Date = ISNULL(oa2.StopDate, oa2.StartDate)
    --??not sure of column name to update or which oa2 date should go first
FROM dbo.tbl_MachineData
OUTER APPLY ( SELECT
... ) oa2 (StartDate, StopDate)
0
SweetingAAuthor Commented:
The output of the original query creates lots of columns but only colomns of interest

- Date
- RunStatus
- StartDate
- StopDate

I want to update tbl_MachineData for every row where....

- Date in tbl_MachineData = date in query
- run status = 0 in tbl_MachineData
- Startdate <> Null in tbl_MachineData

The update should be....

StartDate in tbl_MachineData = StartDate in query result
StopDate in tbl_MachineData = StopDate in query result

Sorry i wasn't specific enough, thanks for the help
0
Scott PletcherSenior DBACommented:
UPDATE md
SET StartDate = oa2.StartDate,
    StopDate = oa2.StopDate
FROM dbo.tbl_MachineData md
OUTER APPLY (SELECT
    (SELECT TOP (1) Date
    FROM dbo.tbl_MachineData t2
    WHERE t2.RunStatus <> dbo.tbl_MachineData.RunStatus
    AND t2.RunStatus = 'TRUE'
    AND t2.Date < md.Date
    ORDER BY t2.Date DESC),
    (SELECT TOP (1) Date
    FROM dbo.tbl_MachineData t2
    WHERE t2.RunStatus <> dbo.tbl_MachineData.RunStatus
    AND t2.RunStatus = 'TRUE' AND t2.Date > md.Date
    ORDER BY t2.Date)
) AS oa2 (StartDate, StopDate)
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

SweetingAAuthor Commented:
I get a few errors trying to run that code

Msg 4104, Level 16, State 1, Procedure stp_UpdateStopTime, Line 21
The multi-part identifier "dbo.tbl_MachineData.RunStatus" could not be bound.
Msg 4104, Level 16, State 1, Procedure stp_UpdateStopTime, Line 23
The multi-part identifier "dbo.tbl_MachineData.Date" could not be bound.
Msg 4104, Level 16, State 1, Procedure stp_UpdateStopTime, Line 28
The multi-part identifier "dbo.tbl_MachineData.RunStatus" could not be bound.
0
Scott PletcherSenior DBACommented:
UPDATE md
 SET StartDate = oa2.StartDate,
     StopDate = oa2.StopDate
 FROM dbo.tbl_MachineData md
 OUTER APPLY (SELECT
     (SELECT TOP (1) Date
     FROM dbo.tbl_MachineData t2
     WHERE t2.RunStatus <> md.RunStatus
     AND t2.RunStatus = 'TRUE'
     AND t2.Date < md.Date
     ORDER BY t2.Date DESC),
     (SELECT TOP (1) Date
     FROM dbo.tbl_MachineData t2
     WHERE t2.RunStatus <> md.RunStatus
     AND t2.RunStatus = 'TRUE' AND t2.Date > md.Date
     ORDER BY t2.Date)
 ) AS oa2 (StartDate, StopDate)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SweetingAAuthor Commented:
Perfect, thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.