April 3, 2014 at 11:31 am
Hi
I want to update one table with the value from variable using a set based approach.
so the line
>> select @List = coalesce(@list + ',','') + cast(id as varchar(10)) + cast(feetype as varchar(25))
works fine but I want to take @list and use it to update my customers table. here is the full code. I could really do with some help!
create table customers
(custid int, fee_history varchar(max))
insert into customers
(custid
, fee_history
)
values (1,null)
, (2,null)
create table fees
(id int
,custid int
,feeType char(25)
)
insert into fees
(id
, custid
,feeType
)
values ('1','1','phonecall')
,('2','1','housecall')
,('3','2','other')
declare @list varchar(max)
select @List = coalesce(@list + ',','') + cast(id as varchar(10)) + cast(feetype as varchar(25))
from fees where custid = 1
print @list
April 3, 2014 at 11:47 am
Try:
UPDATE customers
SET fee_history = STUFF(
(
select ',' + cast(F.id as varchar(10)) + cast(F.feetype as varchar(25))
from fees AS F
where F.custid = customers.custid
ORDER BY F.id
FOR XML PATH(''), TYPE
).value('.', 'nvarchar(MAX)'), 1, 1, '');
I hope you realize that this is not a good idea. Think if in the future you would like to query for customers having specific fee types, how would you do it?
This is one of the inconveniencies of having a not normalized model.
April 3, 2014 at 12:56 pm
Hi
Firstly thanks. Secondly, the example was a simplified version of what I'm trying to do just to get the concept across, the actual tables are a better design.
The suggestion worked great with my simple version but unfortunately the real tables seem to contain charachters which the code doesn't like and I'm getting the following error....
FOR XML could not serialize the data for node 'NoName' because it contains a character (0x0000) which is not allowed in XML. To retrieve this data using FOR XML, convert it to binary, varbinary or image data type and use the BINARY BASE64 directive.
Have tried replace but no joy. Any suggestions please?
April 3, 2014 at 1:43 pm
WADRIAN68 (4/3/2014)
HiFirstly thanks. Secondly, the example was a simplified version of what I'm trying to do just to get the concept across, the actual tables are a better design.
The suggestion worked great with my simple version but unfortunately the real tables seem to contain charachters which the code doesn't like and I'm getting the following error....
FOR XML could not serialize the data for node 'NoName' because it contains a character (0x0000) which is not allowed in XML. To retrieve this data using FOR XML, convert it to binary, varbinary or image data type and use the BINARY BASE64 directive.
Have tried replace but no joy. Any suggestions please?
That is hex for int 0 which is NULL. You can't have a NULL in xml. Do you have any rows where NoName is NULL?
I agree that this is very bad design. You are breaking 1NF by putting multiple values into a single column. :w00t:
_______________________________________________________________
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/
April 3, 2014 at 2:08 pm
Hi Sean
It's not a null, haven't found what it is yet but it's something like a line feed, I'll keep looking...
I'm not planning on storing these values permanently in one column, it's for an extract but I need them as a single string.
thanks
Adrian
April 4, 2014 at 5:15 am
Sean you were correct about the nulls. I discovered a workaround which is detailed below for reference, a fuller explanation is available via the link, perhaps it will be helpful to someone else.
poor design aside, this now works a treat!
Many Thanks.
UPDATE TheTable
SET TheColumn = REPLACE(CAST(TheColumn as varchar(max)) COLLATE SQL_Latin1_General_CP1_CI_AS, CHAR(0), '')
WHERE CAST(TheColumn as varchar(max)) COLLATE SQL_Latin1_General_CP1_CI_AS like '%' + CHAR(0) + '%'
http://blogs.visigo.com/chriscoulson/removing-null-characters-0x00-from-values-in-sql-server/
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply