December 8, 2016 at 11:32 pm
I have a table that has 'Description' column and a view that extracts AccountID from a different database. I would like to replace the 'Description' column with the 'AccountID data - Description Data'.
As an example Description might be '11111 Statement 30 Nov 2016' and the AccountID for the same client is '1000012345' . I want the end result to look like '1000012345 - 11111 Statement 30 Nov 2016'
I have tried
update [Document].[dbo].[DOC.Client]
set [Description] = REPLACE ([Description], [Document].[dbo].[StatementAcNameView].accountid,[description])
but get the message
The multi-part identifier "document.dbo.StatementAcNameView.accountid" could not be bound.
So the question is how do I do this and can I use data from another database to update this database? The only way I can get it to work is to actually enter the actual text for what I want to change and what it changes to and as I have 67,000 records I do not want to do this manually. Any suggestions?:-D
December 9, 2016 at 1:49 am
I have found a solution
update c
set [Description] =
Replace([Description], [Description] ,(t.accountid + ' - ' + [Description]) )
FROM [Document].[dbo].[DOC.Client] as c
inner join [Reporting].[dbo].[Tran] as t
on t.Id = c.accountindex
:-):-):-):-):-)
December 9, 2016 at 7:30 am
You don't need the replace function here. Simply set the Description value to your desired value (in this case, accountID + description)
update c
set [Description] = t.accountid + ' - ' + [Description]
FROM [Document].[dbo].[DOC.Client] as c
inner join [Reporting].[dbo].[Tran] as t on t.Id = c.accountindex
December 9, 2016 at 5:05 pm
Thanks John, I will give it a try.:-)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply