Problem in join update

Question

Problem in join update

DobermannCanis

SSC Enthusiast

Points: 126
More actions
February 28, 2010 at 9:49 am

#211748

I have created the following update:
update a
set a.Test = '-Grid ' + b.comp_ + '- ' + Comments2
from comments2 a, CustGrids2 b
where a.account_id = b.account_id
and comments2 is not null
with the following error:
Msg 8152, Level 16, State 13, Line 1
String or binary data would be truncated.
The statement has been terminated.
All fields being concatenated are nvarchar. Any help is apprecitated.
Thanks,
Dobe

Viewing 15 posts - 1 through 15 (of 21 total)

You must be logged in to reply to this topic. Login to reply

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 1

Dobe,

The message is telling you that the length of the expression '-Grid ' + b.comp_ + '- ' + Comments2 is longer than the length of A.Test. The result won't fit into A.Test without dropping information.

Both VARCHAR and NVARCHAR datatypes can be of varying length, but unless they are VARCHAR(MAX) or NVARCHAR(MAX) then they have a maximum length, which you are exceeding.

You need to change your schema to make the [Test] column longer.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

DobermannCanis SSC Enthusiast Points: 126 More actions · Answer 2

edit: You're correct. Thanks.

Now a problem with null values. The concatenation will not work with null in the comments2 field. How do I make this work?

In other words contatenation + null = null.

sqlvogel SSCrazy Eights Points: 9526 More actions · Answer 3

I suggest you do not use the UPDATE join in SQL Server 2008 because it is effectively superseded by the MERGE statement. Always use MERGE instead unless you need UPDATE for backward-compatibility reasons. MERGE is standard SQL (unlike the UPDATE), more powerful, generally more efficient and doesn't suffer the nasty flaws of Microsoft's proprietary UPDATE syntax. Try:

MERGE INTO comments2 a

USING CustGrids2 b

ON a.account_id = b.account_id

WHEN MATCHED THEN UPDATE

SET Test = '-Grid ' + b.comp_ + '- ' + COALESCE(Comments2,'') ;

(untested)

DobermannCanis SSC Enthusiast Points: 126 More actions · Answer 4

Thanks David, I'll give it a shot. Do you use Merge instead of upate for all "updates"?

sqlvogel SSCrazy Eights Points: 9526 More actions · Answer 5

For new code in 2008 I do, yes. The old-style UPDATE with join will likely be officially deprecated in a future version. It is an unpleasant legacy from SQL Server's old Sybase days.

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 6

David, do you have any source authority that UPDATE is going to be deprecated or is that just a personal opinion? Despite your characterization of it as an unpleasant legacy, UPDATE is ubiquitous in MS-SQL installations. It seems unlikely that MIcrosoft would force a change of that magnitude on its install

base.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 7

Keith,

To handle concatenation of nulls, you need use either the ISNULL() or COALESCE() function. One hint, it is sometimes best to test whether or not an expression is null, not just a column. For example:

ISNULL([lastname]+', ','')+ISNULL([firstname]+' ','')+ISNULL([middleinitial])

This ensures that the last name is always followed by a comma and space, and the first name always followed by a space, but if there is no last name, the comma doesn't appear.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

sqlvogel SSCrazy Eights Points: 9526 More actions · Answer 8

The Dixie Flatline (3/1/2010)
David, do you have any source authority that UPDATE is going to be deprecated or is that just a personal opinion? Despite your characterization of it as an unpleasant legacy, UPDATE is ubiquitous in MS-SQL installations. It seems unlikely that MIcrosoft would force a change of that magnitude on its install
base.

What Microsoft have said is:

"We are indeed looking at the future of the FROM clause ... The earliest we could start the deprecation process at the moment is in the release after 2008."

http://connect.microsoft.com/SQLServer/feedback/details/332437/deprecate-update-from-and-delete-from

It needs to be done in my opinion. UPDATE with FROM...JOIN is defective to the extent that it gives unreliable results. I expect that the cost of Microsoft preserving the unusual behaviour of those updates across future versions would eventually become too onerous. At some point the price paid to support the legacy form of UPDATE would probably put a brake on other features. Other poorly designed aspects of T-SQL have been deprecated in the past for similar reasons.

Only UPDATE with a FROM clause using multiple tables needs to be deprecated. UPDATE with a single table or with subqueries is OK. That would not be as disruptive as some other changes that Microsoft have already said they will make. For instance requiring statements to be terminated with semi-colons will be a bigger change.

In the case of UPDATE the impact is justified because MERGE really is a much better solution all round. In any case, I hope and expect that for a time period there would be a compatibility switch to turn the deprecated syntax on or off - if and when deprecation does happen that is.

HowardW One Orange Chip Points: 29109 More actions · Answer 9

I honestly cannot see them even deprecating UPDATE FROM, let alone removing it from the product. I think it's crazy to avoid using it in code just because someone said they're discussing it on a Microsoft forum.

I don't even see MERGE as a viable alternative as it forces a table scan. It's also overkill if all you want to do is update from one table to another and not do deletes/inserts.

sqlvogel SSCrazy Eights Points: 9526 More actions · Answer 10

To be clear, I'm not saying you should avoid UPDATE FROM just because Microsoft may deprecate it. I'm recommending that you should avoid it because MERGE is more efficient, more powerful, more reliable and safer (it doesn't have the Sybase UPDATE's bug-feature that causes unpredictable results). It is also mostly portable and is more widely understood by database developers from its use in other DBMSs.

MERGE does not force a table scan, I'm not sure why you think it does. Do you have a particular scenario in mind? In my experience MERGE gives a more efficient query plan than UPDATE with a join in most cases. As with any other statement, the rows affected by MERGE are specified as a search condition so it certainly should not need to touch every row of a table.

Why do you think MERGE is "overkill" for updates? The Insert and Delete clauses are optional. If you don't need them then leave them out. True you have to type a few extra characters for "WHEN MATCHED THEN" but given SQL's generally verbose syntax I think we're used to coping with much worse!

HowardW One Orange Chip Points: 29109 More actions · Answer 11

From BOL:

http://technet.microsoft.com/en-us/library/bb510625.aspx

Because the MERGE statement performs a full table scan of both the source and target tables, I/O performance can be affected when using the TOP clause to modify a large table by creating multiple batches. In this scenario, it is important to ensure that all successive batches target new rows. For more information, see Optimizing MERGE Statement Performance.

I think if you understand the limitations of UPDATE FROM where there's not a one-to-one relationship between the tables, it's a perfectly good, succinct way of performing an update.

sqlvogel SSCrazy Eights Points: 9526 More actions · Answer 12

HowardW (3/1/2010)
From BOL:
http://technet.microsoft.com/en-us/library/bb510625.aspx
Because the MERGE statement performs a full table scan of both the source and target tables, I/O performance can be affected when using the TOP clause to modify a large table by creating multiple batches. In this scenario, it is important to ensure that all successive batches target new rows. For more information, see Optimizing MERGE Statement Performance.

Taken out of context I can see how that seems misleading. The link given in that paragraph explains that by specifying the search conditions it won't do that.

I think if you understand the limitations of UPDATE FROM where there's not a one-to-one relationship between the tables, it's a perfectly good, succinct way of performing an update.

Perhaps, but you do pay a price in poorer performance and reduced features and flexibility.

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 13

Thanks to you both for posting your sources. It's always much better to see a cite something than to rely on "I heard that... " statements.

That said, I've never experienced unreliable results with an UPDATE...FROM. I also use MERGE as well, and am glad to have it, although I never before considered using it for updates only. Time to do a little testing. This shouldn't rise to the level of political or religious differences. 😉

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

HowardW One Orange Chip Points: 29109 More actions · Answer 14

Perhaps, but you do pay a price in poorer performance and reduced features and flexibility.

Could you point me in the direction of a source for MERGE having better performance than UPDATE FROM?