SweetingA
asked on
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.RunSta
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.RunSta
AND t2.RunStatus = 'TRUE' AND t2.Date > dbo.tbl_MachineData.Date
ORDER BY t2.Date) ) oa2 (StartDate, StopDate)
Thanks in advance
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.RunSta
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.RunSta
AND t2.RunStatus = 'TRUE' AND t2.Date > dbo.tbl_MachineData.Date
ORDER BY t2.Date) ) oa2 (StartDate, StopDate)
Thanks in advance
ASKER
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
- 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
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.RunSta
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.RunSta
AND t2.RunStatus = 'TRUE' AND t2.Date > md.Date
ORDER BY t2.Date)
) AS oa2 (StartDate, StopDate)
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.RunSta
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.RunSta
AND t2.RunStatus = 'TRUE' AND t2.Date > md.Date
ORDER BY t2.Date)
) AS oa2 (StartDate, StopDate)
ASKER
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.RunSt
Msg 4104, Level 16, State 1, Procedure stp_UpdateStopTime, Line 23
The multi-part identifier "dbo.tbl_MachineData.Date"
Msg 4104, Level 16, State 1, Procedure stp_UpdateStopTime, Line 28
The multi-part identifier "dbo.tbl_MachineData.RunSt
Msg 4104, Level 16, State 1, Procedure stp_UpdateStopTime, Line 21
The multi-part identifier "dbo.tbl_MachineData.RunSt
Msg 4104, Level 16, State 1, Procedure stp_UpdateStopTime, Line 23
The multi-part identifier "dbo.tbl_MachineData.Date"
Msg 4104, Level 16, State 1, Procedure stp_UpdateStopTime, Line 28
The multi-part identifier "dbo.tbl_MachineData.RunSt
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Perfect, thanks
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)