update query not working

Question

The below query is throwing:

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

update table1
set cust_line1_name = c.cust_line1_name
from table1 d
inner join dbo.table2 c
on c.cust_nbr = d.cust_id
and c.bus_grp_id = d.bus_grp_id

table1

 cust_line1_name - varchar(24)

table2

 cust_line1_name - nvarchar(140)

I checked whether any data which is having length greater than 24 is getting updated but there is no such data

SELECT LEN(c.cust_line1_name), c.cust_line1_name
from table1 d
inner join dbo.table2 c
on c.cust_nbr = d.cust_id
and c.bus_grp_id = d.bus_grp_id
WHERE  LEN(c.cust_line1_name)> 24

The above query is returing zero result. The issue is in SQL server 2008 Could someone help

I think you have missed a link between table1 and tables d and c — Joe Taras
– Joe Taras, Commented Mar 3, 2016 at 14:27

Max Sorin · Accepted Answer · 2016-03-03 14:44:49Z

3

Converting to varchar from nvarchar is going to lose the data that isn't able to be converted. Anything in a foreign language from table2.cust_line1_name (nvarchar(140)) would not fit into your varchar 24

The correct course of action:

if table1 column has data:

Add a nvarchar(140) column to table 1
Copy data from current varcahar(24) column
Drop varchar(24) column
Rename the new nvarchar(140) column to old column name

If the table1 column has no data just drop it, and make your new column for populating with data from table2 the same size and data type as the one in table2.

edited Mar 3, 2016 at 14:44

answered Mar 3, 2016 at 14:38

Max Sorin

1,0526 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Joe Taras · Accepted Answer · 2016-03-03 14:30:17Z

0

You have missed the link between your update table (table1) and two tables in from clause:

Your query:

update table1
set cust_line1_name = c.cust_line1_name
from table1 d
inner join dbo.table2 c
on c.cust_nbr = d.cust_id
and c.bus_grp_id = d.bus_grp_id

You must add a WHERE condition to decide how you want to update your table1

For example, you can add

WHERE table1.id = d.id

Because d is the same table (table1)

Otherwise, how you have written your query, you have tried to put in a scalar field a list of rows (result of join between d and c table)

answered Mar 3, 2016 at 14:30

Joe Taras

15.4k7 gold badges45 silver badges59 bronze badges

2 Comments

Thomas Over a year ago

i added where still it is not working update table1 set cust_line1_name = c.cust_line1_name from table1 d inner join dbo.table2 c on c.cust_nbr = d.cust_id and c.bus_grp_id = d.bus_grp_id WHERE c.cust_nbr = d.cust_id

Joe Taras Over a year ago

@Thomas: No, you're wrong, not c.cust_nbr = d.cust_id, but you must link table1.cust_id = d.cust_id

GigiS · Accepted Answer · 2016-03-03 17:01:49Z

0

update table1
set cust_line1_name = left(c.cust_line1_name, 24)
from table1 d, dbo.table2 c
Where c.cust_nbr = d.cust_id
and c.bus_grp_id = d.bus_grp_id

answered Mar 3, 2016 at 17:01

GigiS

665 bronze badges

Comments

k4ppa · Accepted Answer · 2016-03-04 09:26:55Z

0

make a change in the condition and then it works fine. Query is:

UPDATE table1
        SET cust_line1_name = c.cust_line1_name
        FROM table1 d INNER JOIN dbo.table2 c
        ON c.cust_nbr = d.cust_id
        AND c.bus_grp_id = d.bus_grp_id
        AND  LEN(c.cust_line1_name)<= 24

edited Mar 4, 2016 at 9:26

k4ppa

4,7478 gold badges29 silver badges39 bronze badges

answered Mar 4, 2016 at 9:15

moks

995 bronze badges

Collectives™ on Stack Overflow

update query not working

4 Answers 4

Comments

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related