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.
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);