Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Update question

Author  Topic 

mvazquezfiallo
Starting Member

1 Post

Posted - 2008-02-11 : 12:37:25
Hello All.

This is my update:

UPDATE WIRES SET WGENEDATE = CONVERT(VARCHAR,GETDATE(),101),
WLOGCHANGE = convert(varchar(8000),WLOGCHANGE) + '*AUTO*, '+substring(CAST(MONTH({fn NOW()})+100 AS varchar),2,2)+'/'+
substring(CAST(DAY({fn NOW()})+100 AS varchar),2,2)+'/'+substring(CAST(YEAR({fn NOW()}) AS varchar),3,2)+', '+
substring(CAST(DATEPART(hh, { fn NOW() })+100 AS varchar),2,2) + ':' + substring(CAST(DATEPART(mi, { fn NOW() })+100 AS varchar),2,2) +
':' + substring(CAST(DATEPART(ss, { fn NOW() })+100 AS varchar),2,2)+', Generation date updated'+CHAR(13)+CHAR(10)
WHERE (DATEDIFF(DD,WLOADDATE, GETDATE()) <= 5) and WGENEDATE='1/1/1900' AND wgenetime <> '' AND wlogchange LIKE '%GENERATED%'

Why sometimes I am getting this error:

"Subquery 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."

Thank you.

Qualis
Posting Yak Master

145 Posts

Posted - 2008-02-11 : 13:06:49
Try

UPDATE WIRES SET
WGENEDATE = CONVERT(VARCHAR,GETDATE(),101),
WLOGCHANGE = convert(varchar(8000),WLOGCHANGE) + '*AUTO*, '+
substring(CAST(MONTH(GETDATE())+100 AS varchar),2,2)+'/'+
substring(CAST(DAY(GETDATE())+100 AS varchar),2,2)+'/'+
substring(CAST(YEAR(GETDATE()) AS varchar),3,2)+', '+
substring(CAST(DATEPART(hh, GETDATE())+100 AS varchar),2,2) +
':' + substring(CAST(DATEPART(mi, GETDATE())+100 AS varchar),2,2) +
':' + substring(CAST(DATEPART(ss, GETDATE())+100 AS varchar),2,2)+
', Generation date updated'+CHAR(13)+CHAR(10)
WHERE
(DATEDIFF(DD,WLOADDATE, GETDATE()) <= 5) and
WGENEDATE='1/1/1900' AND
wgenetime <> '' AND
wlogchange LIKE '%GENERATED%'

I replaced { fn NOW() } with GetDate().

http://www.dbnewsgroups.net/group/microsoft.public.sqlserver.programming/topic336.aspx
Go to Top of Page
   

- Advertisement -