Sql-server – Update using Sub Select SQLServer

sql serversql-server-2012

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.

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

Related Solutions

Sql-server – SQL Select taking too much time to execute

I think in a million records query, you have to avoid things like OUTER JOINS. I suggest you use UNION ALL Instead of LEFT JOIN. As long as I think CROSS APPLY is more efficient than sub-query in the select clause I will modify the query written by Conard Frix, which I think is correct.

now: when I started to modify your query I noticed that you have a WHERE clause saying: JoinedTable.WhereColumn IN (1, 3). in this case, if the field is null the condition will become false. then why are you using LEFT JOIN while you are filtering null valued rows? just replace LEFT JOIN With INNER JOIN, I guarantee that it will become faster.

about INDEX:

please note that when you have an index on a table, say

table1(a int, b nvarchar)

and your index is :

nonclustered index ix1 on table1(a)

and you want to do something like this:

select a,b from table1
where a < 10

in your index you have not included the column b so what happens?

if sql-server uses your index, it will have to search in the index, called "Index Seek" and then refer to main table to get column b, called "Look Up". This procedure might take much longer than scanning the table itself: "Table Scan".

but based on the statistics that sql-server has, in such situations, it might not use your index at all.

so first of all check the Execution Plan to see if the index is used at all.

if yes or no both, alter your index to include all columns that you are selecting. say like:

nonclustered index ix1 on table1(a) include(b)

in this case Look Up will not be needed, and your query will execute so much faster.

Sql-server – How to improve this query (using table variables or not?)

You can do this with a correlated sub query.

SELECT Cost, 
       Cost * (SELECT TOP 1 Percentt 
               FROM   Taxes t 
               WHERE  t.MonthsId <= tc.MonthsId 
               ORDER  BY t.MonthsId DESC) AS TaxedCost 
FROM   TableCost tc

You may well get a more efficient plan though if you simply fill in the gaps in your Taxes table so you can do a straight forward join on MonthsId. At least that should allow the optimiser to consider something other than nested loops.

Best Answer

Related Solutions

Sql-server – SQL Select taking too much time to execute

Sql-server – How to improve this query (using table variables or not?)

Related Question