T-SQL Concatenting Column and a String Value

Hello:

I have the following general ledger accounts:

000-TEST-00
000-TEST-01

Each account contains--as its description--"Test Account".

I need for the description to instead read "USA - 'last segment of the account number'".

So, the two accounts above would have the following descriptions respectively:

USA - 00
USA - 01

So, I created two update statements.  Here is the first one that ran successfully:

UPDATE GL00100 set ACTDESCR = 'USA' where ACTNUMBR_2 = 'TEST'

Then, I ran a second update statement which produced a "subquery cannot return more than one value" error:

UPDATE GL00100 set ACTDESCR = (select actdescr from GL00100 where ACTNUMBR_2 = 'TEST')+' '+'-'+' '+(SELECT ACTNUMBR_3 FROM GL00100 where ACTNUMBR_2 = 'TEST')
where ACTNUMBR_2 = 'TEST'

I know that there are several of the same "where" clauses.  But, I'm trying to be careful not to update all account numbers in GL00100.

Anyway, how do I get the second update statement to work?

Thanks!

TBSupport
LVL 1
TBSupportAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Wow that's ugly.

So .. both subqueries uses only table GL00100, and the update changes values in GL00100.  Shouldn't need subqueries at all.

For starters, what would happen if this returns more than one value?
select actdescr from GL00100 where ACTNUMBR_2 = 'TEST'

Open in new window

0
Simone BSenior E-Commerce AnalystCommented:
If I understand correctly, you have changed the relevant records description to USA, now you want to append ACTNUMBR_3 to them.

First, it's best to make sure you have a backup before running any update statements, just in case you run into any problems.

Try this select first to ensure you are getting only the ones you want to change:

SELECT * FROM GL00100
WHERE ACTDESCR = 'USA' AND ACTNUMBR_2 = 'TEST'

Open in new window


If these are the correct records that you want to change, then you can run the update statement below.

UPDATE GL00100
SET ACTDESCR = ACTDESCR + ' - ' + ACTNUMBR_3
WHERE ACTDESCR = 'USA' AND ACTNUMBR_2 = 'TEST'

Open in new window

0
TBSupportAuthor Commented:
Hi Buttercup1:

Your final query is what I want.  But, when I run it, I get the following error:

Msg 8152, Level 16, State 14, Line 1
String or binary data would be truncated.
The statement has been terminated.

Any ideas?

TBSupport
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Simone BSenior E-Commerce AnalystCommented:
I think Great Plains has char fields, which means you may have to remove the trailing spaces. Try this:

UPDATE GL00100
SET ACTDESCR = rtrim(ACTDESCR) + ' - ' + rtrim(ACTNUMBR_3)
WHERE ACTDESCR = 'USA' AND ACTNUMBR_2 = 'TEST' 

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Simone BSenior E-Commerce AnalystCommented:
Or this:


UPDATE GL00100
SET ACTDESCR = rtrim(cast(ACTDESCR as varchar(3))) + ' - ' + rtrim(cast(ACTNUMBR_3 as varchar(5)))
WHERE ACTDESCR = 'USA' AND ACTNUMBR_2 = 'TEST'

Open in new window

0
TBSupportAuthor Commented:
Thanks, Buttercup1!

John
0
Simone BSenior E-Commerce AnalystCommented:
You're welcome!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SSRS

From novice to tech pro — start learning today.