Update using Sub Select SQLServer

Posted on

Question :

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.

Answer :

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

Leave a Reply

Your email address will not be published.