February 5, 2008 at 10:38 am
Hi I want to update a column with date+anothercolumn info where the column IS NULL. Updates I tried wouldnt allow me as it returns more than one row. I therefore created a sep temp table with all the trans_numbers I wanted to update so I could join to it, but still the same..?? I tried..
update header set Siebeltransnumber = (
selectCONVERT (char(6),h.trans_date,12)
+substring (h.trans_number, patindex('%[^0]%', h.trans_number), 20)
from header h
where Siebeltransnumber IS NULL)
..but got the 'more than 1 row' error. So tried...
update header set Siebeltransnumber = (
selectCONVERT (char(6),h.trans_date,12)
+substring (h.trans_number, patindex('%[^0]%', h.trans_number), 20)
from header h join testh t1
on h.trans_number = t1.trans_number
)
testh just contains a list of trans_numbers in header that I want to update. But got the same error. Can someone please let me know where I'm going wrong, sorry, this seems so basic!!!
thanks
February 5, 2008 at 12:33 pm
It seems your query did not return unique value. Here is my input:
Run
select CONVERT (char(6),h.trans_date,12)
+substring (h.trans_number, patindex('%[^0]%', h.trans_number), 20)
from header h
where Siebeltransnumber IS NULL
and
select DISTINCT CONVERT (char(6),h.trans_date,12)
+substring (h.trans_number, patindex('%[^0]%', h.trans_number), 20)
from header h
where Siebeltransnumber IS NULL
to see whether or not they return the same numbers of rows.
February 6, 2008 at 2:13 am
Hi, yes they are all distinct values. Again, if I place the update statement in front of it I still get the error - 'Subquery returned more than 1 value'
thanks
February 6, 2008 at 2:24 am
How about just
update header set Siebeltransnumber = CONVERT (char(6),h.trans_date,12)
+substring (h.trans_number, patindex('%[^0]%', h.trans_number), 20)
where Siebeltransnumber IS NULL
The reason you're getting errors is that SQL was trying to evaluate the subquery (which would return all the rows where the Siebeltransnumber is null), for each row of the table. It would have updated each row, because the where was in the subquery, not in the update and there was no join between the updated table and the subquery
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 6, 2008 at 2:46 am
Opps of course, just got that. :blush: Thanks for confirming Gail
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply