[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 180
  • Last Modified:

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
0
SweetingA
Asked:
SweetingA
  • 3
  • 3
1 Solution
 
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
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
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
 
SweetingAAuthor Commented:
Perfect, thanks
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now