CODEWITHSUNDEEP
sql-serversql-server-2008
Thomas
Thomas

Reputation: 213

update query not working

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

Upvotes: 2

Views: 175

Answers (4)

moks
moks

Reputation: 99

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

Upvotes: 0

GigiS
GigiS

Reputation: 66

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

Upvotes: 0

Max Sorin
Max Sorin

Reputation: 1052

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:

  1. Add a nvarchar(140) column to table 1
  2. Copy data from current varcahar(24) column
  3. Drop varchar(24) column
  4. 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.

Upvotes: 3

Joe Taras
Joe Taras

Reputation: 15379

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)

Upvotes: 0

Related Questions