December 27, 2005 at 12:43 pm
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
December 27, 2005 at 1:16 pm
One 'weird' thing about SQL Server and aliases, is that you can't use an alias in the UPDATE line.
So, "UPDATE t_remarks te" won't work, but "UPDATE t_remarks" will work. Try this:
update t_remarks
set out_date =
(select TOP 1 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)
-SQLBill
December 27, 2005 at 2:17 pm
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
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply