asked on

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

Jim Horn

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

Simone B

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

TBSupport

ASKER

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

ASKER CERTIFIED SOLUTION

Simone B

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Simone B

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

TBSupport

ASKER

Thanks, Buttercup1!

John

Simone B

You're welcome!