Update clause using if condition
-
I have written following sqlstatements to update a column based on the no of days between two dates. but it is giving the error, Server: Msg 512, Level 16, State 1, Line 2 Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. In what other way i can get the expected result if (select datediff(dd,transactiondate, Issuedate) from receipt)>365 begin update receipt set longvalue=datediff(dd,transactiondate, Issuedate) end else begin update receipt set shortvalue=datediff(dd,transactiondate, Issuedate) end
Chaitra N
-
I have written following sqlstatements to update a column based on the no of days between two dates. but it is giving the error, Server: Msg 512, Level 16, State 1, Line 2 Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. In what other way i can get the expected result if (select datediff(dd,transactiondate, Issuedate) from receipt)>365 begin update receipt set longvalue=datediff(dd,transactiondate, Issuedate) end else begin update receipt set shortvalue=datediff(dd,transactiondate, Issuedate) end
Chaitra N
Use a WHERE in the UPDATE
UPDATE receipt set longvalue=datediff(dd,transactiondate, Issuedate)
WHERE datediff(dd,transactiondate, Issuedate) >365UPDATE receipt set shortvalue=datediff(dd,transactiondate, Issuedate)
WHERE datediff(dd,transactiondate, Issuedate) <= 365
Upcoming events: * Glasgow: Mock Objects, SQL Server CLR Integration, Reporting Services, db4o, Dependency Injection with Spring ... "I wouldn't say boo to a goose. I'm not a coward, I just realise that it would be largely pointless." My website