Update Query Help

Transact-SQL
1

Hi, I'm attempting to update multiple records using the query below:

--select n.id, email,member_type,first_name, last_name,left(first_name,1)+last_name,u.*--
update u set u.web_login=n.email, u.password=left(first_name,1)+last_name
from name n
join Name_security u on n.id=u.id
where n.email<>' ' and web_login<>' ' and u.password=' ' --and u.id='10015'--

When I run the query on a single record it updates fine. But when I run it on the group it returns the error below:

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

Any help would be appreciated.

2

either the email column or the password column is shorter that the data coming in. Either increase the length of the destination columns or truncate the data in the set expressions.

1 Like
3

Ok. Rather than increasing the field size -

How do I tell it to set the password (see below: u.password) using the first 3 letters of the first_name & the first 3 letters of the last_name?

update u set u.web_login=n.email, u.password=left(first_name,1)+last_name

DJpin

4

update u set u.web_login=n.email, u.password=left(first_name,3)+left(last_name,3)

1 Like
5

Thank Scott - that worked on the individual id record but I still get the same "truncation error" on the mass update.

I even did:

update u set u.web_login=n.email, u.password=Batboy1
from name n
join Name_security u on n.id=u.id
where n.email<>' ' and web_login<>' ' and u.password=' ' --and u.id='10045'

And it worked on the single id but crashed when I commented out the id.

I'm totally at a loss. :frowning:

6

Gbritton: How do I truncate the data in the set expression?

Djpin

7

set col = left(value, nn)

1 Like
8

I was able to find the issue:

I just ran “select len(email) from name when len(email) > 60” on the test dbase and found 6 recs.

Thanks for your help!

DJ