0

I have a table i'm trying to update, where i'm adding a prefix to some records, depending on whether or not, the value appears in another table. For example, I have a region_ref, and region_no, and I want to update all of the regions, if the region_no exists in another table.

The problem is, if i were to update all records, it would fail on some, due to the column length. However, i'm not updating these records as they dont appear in the sub select (these records dont appear in the sub select if i run it standalong.

update emp
set region_ref = 'UK_' + emp.REGION_REF 
from EMPLOYEES emp
inner join REGIONS reg
on emp.region_no=reg.REGION_NO
where emp.region_no in (select region_no from UKREGIONS);

There are two records in the employees table which are already at the max value, which I shouldnt be updating anyway. Any ideas? Its almost like the update statement is being validated without the sub select.

  • Maybe an additional condition in where clause , like AND LEN(emp.region_ref)<200 ; when you say, max value at what do you refer ? can you give an example – Sabin Bio Apr 20 '17 at 12:38
  • 1
    Could you post the specific error you are seeing when you perform this UPDATE? – AMtwo Apr 20 '17 at 12:39
  • 1
    do you get the rows you expect if you run the UPDATE as a SELECT instead? e.g. select * from EMPLOYEES emp inner join REGIONS reg on emp.region_no=reg.REGION_NO where emp.region_no in (select region_no from UKREGIONS); – motosubatsu Apr 20 '17 at 13:17
  • Could you provide us with an example of the data and possibly include the involved column definitions so that we can assist you? – John aka hot2use Apr 20 '17 at 14:09
1

As a workaround, try using the RIGHT function to avoid the length overflow.

Just drop whatever the max length of your (I'm assuming) CHAR column in the query where I made a note.

update emp
set region_ref = RIGHT('UK_' + emp.REGION_REF , /*put the max length of your column here*/)
from EMPLOYEES emp
inner join REGIONS reg
on emp.region_no=reg.REGION_NO
where emp.region_no in (select region_no from UKREGIONS);
  • Neat idea Erik. More seamless than checking information_schema.columns. – scsimon Apr 20 '17 at 14:02
  • Possibly could lead to UK_UK_WEST_YORKSHIRE values in the column if the column is say nvarchar(20) and the REGION_REF was 14 characters in length during the first run. – John aka hot2use Apr 20 '17 at 14:04
  • @hot2use Maybe? I can't see the data. – Erik Darling Apr 20 '17 at 14:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.