Updating a record from table with data from a column of another record from the same table
Hey guys,
I have a table say as follows
id, id2, field1, field2, date1, date2
I want to update the date1 field to the date2 field of the very next record with the same id2. In short I want to update a record id(x) by putting in the date2 field if id(y) in the date1 of id(x) where id2(x) = id2(y) and id(y) > id(x) (as id is a sequence). I have written a query as below:
update t_remarks te
set out_date =
(select TOP 1 date_remark_entered from t_remarks ta where ta.record_id = te.record_id and ta.remark_id > te.remark_id order by ta.remark_id)
the above query throws an error:
Line 1: Incorrect syntax near 'te'.
Incorrect syntax near the keyword 'order'.
If I do not Order the selected records then it would return the record which may not necessarily be the next record with the same id2. Could anyone please point me towards why the error is occuring
Thanks,
Prashant
Updating a record from table with data from a column of another record from the same table
MichaelLatta
Thanks for your reply. I used the above query, but I get an error saying that the subquery returned more than one record.
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
I don't understand the reason for this when the "TOP 1" and "Order by" clauses are specified.
-Prashant
Jay_187
venkee
update t_remarks
set out_date =
(select TOP 1 ta.date_remark_entered
from t_remarks ta
where ta.record_id = t_remarks.record_id
and ta.remark_id > t_remarks.remark_id
order by ta.remark_id)
The table script is:
if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[t_remarks]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[t_remarks] (
[remark_id] [int] IDENTITY (1, 1) NOT NULL ,
[remark] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[record_id] [int] NULL ,
[remark_type] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[date_remark_entered] [datetime] NULL ,
[remark_entered_by_cuid] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[remark_entered_by_name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[status_dd_id] [int] NULL ,
[out_date] [datetime] NULL ,
CONSTRAINT [PK_t_remarks] PRIMARY KEY CLUSTERED
(
[remark_id]
) WITH FILLFACTOR = 90 ON [PRIMARY] ,
CONSTRAINT [FK_t_remarks_t_main] FOREIGN KEY
(
[record_id]
) REFERENCES [dbo].[t_main] (
[id]
) NOT FOR REPLICATION
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
CREATE INDEX [date_remark_enteredidx] ON [dbo].[t_remarks]([date_remark_entered]) ON [PRIMARY]
CREATE INDEX [record_ididx] ON [dbo].[t_remarks]([record_id]) ON [PRIMARY]
CREATE INDEX [rec_remark] ON [dbo].[t_remarks]([record_id], [remark_type]) ON [PRIMARY]
END
JARP2006
Jan tje
set out_date =
(select TOP 1 ta.date_remark_entered