hi, i have a query, well a sp, that i am trying to run to update the contents of a table by compairing two timestamps with a date diff. for the life of me i cannot get this sp to work correctly. I can run the query to return the results i want but not to do the update and record them to another col...this is the functional query:-SELECT CONVERT(float, DATEDIFF(ms, (SELECT MAX(_TIMESTAMP) AS Expr1 FROM [m_data_aq].[dbo].t_idi_37 WHERE (_VALUE = 1) AND (_TIMESTAMP < T ._TIMESTAMP)), _TIMESTAMP)) / 1000 AS cycle_time FROM [m_data_aq].[dbo].t_idi_37) AS TWHERE (_VALUE = '1')
this is the sp i am trying to create to make the updates i requireset ANSI_NULLS ONset QUOTED_IDENTIFIER ONGOALTER PROCEDURE [dbo].[sp_insert_idi_10_data]as UPDATE t_idi_37SET cycle_time = (SELECT CONVERT(float, DATEDIFF(ms, (SELECT MAX(_TIMESTAMP) AS Expr1 FROM [m_data_aq].[dbo].t_idi_37 WHERE (_VALUE = 1) AND (_TIMESTAMP < T ._TIMESTAMP)), _TIMESTAMP)) / 1000 AS cycle_time FROM [m_data_aq].[dbo].t_idi_37 AS TWHERE (_VALUE = '1'))
when executing the sp i get the following result/errorMsg 512, Level 16, State 1, Procedure sp_insert_idi_10_data, Line 3Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.The statement has been terminated.(0 row(s) affected)(1 row(s) affected)
now this is probably a bit strong for my sql skills, maybe not 5 years ago but not touched it since so to say i am rusty is an understatement!!!If anyone can point me in the right direction i would be really greatful!here by the way this is a sample of the data set i am working with[URL=http://img192.imageshack.us/i/tableik.jpg/]
[/URL]
updating a record with the results of a query
Posted - 2010-08-25 : 02:18:33
