August 17, 2012 at 9:02 am
Good Morning Guys,
i wanted to ask you guys on your 2 cents on this. i was trying to run an Update Query with a Subquery and did not allow me to have multiple updates on the table. the sql script is shown below
Update tbleventsPCRInformation
set DOB = (Select Cast(('1/01/'+ Cast(Year(DOB)- 1
as varchar(20))+ ' 12:00:00 AM') as DateTime)
as ConvertedDOB
From tbleventsPCRInformation where DOB is Not Null)
where DOB = (Select DOB from tbleventsPCRInformation where DOB is Not Null)
it returned with an error saying
Msg 512, Level 16, State 1, Line 1
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.
any help you can extend is very much appreciated.
Much thanks
Noel
August 17, 2012 at 9:12 am
Stylez (8/17/2012)
Good Morning Guys,i wanted to ask you guys on your 2 cents on this. i was trying to run an Update Query with a Subquery and did not allow me to have multiple updates on the table. the sql script is shown below
Update tbleventsPCRInformation
set DOB = (Select Cast(('1/01/'+ Cast(Year(DOB)- 1
as varchar(20))+ ' 12:00:00 AM') as DateTime)
as ConvertedDOB
From tbleventsPCRInformation where DOB is Not Null)
where DOB = (Select DOB from tbleventsPCRInformation where DOB is Not Null)
it returned with an error saying
Msg 512, Level 16, State 1, Line 1
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.
any help you can extend is very much appreciated.
Much thanks
Noel
Okay, so you are trying to set DOB to January 1, (of what ever year) for all records where DOB is not null. Is that correct?
August 17, 2012 at 9:22 am
Hi Lynn. Yes, I was trying to Set All The Dates To January 1 (For Data Scrubbing Purposes). i am also thinking if a trigger is causing this error? any 2 cents on this?
Much Thanks
Noel
August 17, 2012 at 9:27 am
Stylez (8/17/2012)
Hi Lynn. Yes, I was trying to Set All The Dates To January 1 (For Data Scrubbing Purposes). i am also thinking if a trigger is causing this error? any 2 cents on this?Much Thanks
Noel
All you need is this:
UPDATE tbleventsPCRInformation SET
DOB = dateadd(yy, datediff(yy,0,DOB), 0)
WHERE
DOB is not null;
August 17, 2012 at 9:41 am
Hi Lynn,
This Is Awesome!!! Much Thanks
Best Regards,
Noel
August 17, 2012 at 9:45 am
Lynn Pettis (8/17/2012)
Stylez (8/17/2012)
Hi Lynn. Yes, I was trying to Set All The Dates To January 1 (For Data Scrubbing Purposes). i am also thinking if a trigger is causing this error? any 2 cents on this?Much Thanks
Noel
All you need is this:
UPDATE tbleventsPCRInformation SET
DOB = dateadd(yy, datediff(yy,0,DOB), 0)
WHERE
DOB is not null;
You could even leave off the where clause if you wanted. The date math will just set it to NULL. 😉
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 17, 2012 at 9:56 am
Sean Lange (8/17/2012)
Lynn Pettis (8/17/2012)
Stylez (8/17/2012)
Hi Lynn. Yes, I was trying to Set All The Dates To January 1 (For Data Scrubbing Purposes). i am also thinking if a trigger is causing this error? any 2 cents on this?Much Thanks
Noel
All you need is this:
UPDATE tbleventsPCRInformation SET
DOB = dateadd(yy, datediff(yy,0,DOB), 0)
WHERE
DOB is not null;
You could even leave off the where clause if you wanted. The date math will just set it to NULL. 😉
True, but depending on how many rows where DOB is null, it reduces the number of rows modified keeping the t-log smaller.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply