June 19, 2011 at 1:02 am
Hi,
If we consider the below:
declare @m as table (a int, b int)
declare @n as table (c int, d int)
insert into @m
select 1,2
union all
select 2,3
insert into @n
select 1,0
union all
select 2,5
update @m
set b = (select d from @n where a = c) where b = 3 and (select d from @n where a = c) > 0
select * from @m
select * from @n
I am looking at updating to @m from @n but not only when @m satisfies the condition of b = 3, I also want @m to satisfy the condition of d > 0
However, I feel the syntax is untidy, and I have got an extra select statement that will no doubt slow down progress. Can anyone tell me a neater way to write this, or is this pretty much the correct technique for the problem. To give you and idea I tried and failed with:
set b = (select d from @n where a = c) where b = 3 and [@n].d > 0
Many Thanks,
Matthew
June 19, 2011 at 1:36 am
Sorry, but I'm confused as to what you are trying to accomplish. You provide some very basic table structures and sample data but you really to show what the expected output should be.
June 19, 2011 at 3:27 am
HI, i am not too sure what you mean either however, looking at your set statement it might be worth you looking up CASE statements
for example i have a test table with the following values, test, test2, test 3
where the value = test i want it to update them to comp, test 2 to yup and so on
update #temp set test = case test when 'test' then 'comp'
when 'test2' then 'yup'
when 'test3' then 'last'
end
***The first step is always the hardest *******
June 19, 2011 at 3:42 am
Lynn Pettis (6/19/2011)
Sorry, but I'm confused as to what you are trying to accomplish. You provide some very basic table structures and sample data but you really to show what the expected output should be.
Hi,
What I am looking for is a neater way of scripting, where I want to update table @m from data in table @n, but have criteria on table @n as to whether it updates or not.
update @m set b = (select d from @n where a = c) where b = 3 and (select d from @n where a = c) > 0
In my statement above, I am saying only update @m when the potential value from @n is > 0.
So, I have the condition: (select d from @n where a = c) > 0
I'm trying to work out whether I can simplify the statement by bringing the condition without the additional select.
June 19, 2011 at 6:42 am
Here are a couple ways to get there.
On 2005 you can try the UPDATE...JOIN syntax which is proprietary to SQL Server. It's not ANSI compliant and it suffers from a problem when more than one row is matched and qualifies for update in that the engine is forced to choose a row to use for update but it does not report any warning or error:
DECLARE @m AS TABLE ( a INT, b INT ) ;
DECLARE @n AS TABLE ( c INT, d INT ) ;
INSERT INTO @m SELECT 1,2 UNION ALL SELECT 2,3 ;
INSERT INTO @n SELECT 1,0 UNION ALL SELECT 2,5 ;
SELECT 'm', * FROM @m ;
SELECT 'n', * FROM @n ;
UPDATE m
SET b = n.d
FROM @m m
JOIN @n n ON m.a = n.c
WHERE b = 3
AND d > 0 ;
SELECT 'm', * FROM @m ;
GO
When you're on 2008 you can use the MERGE syntax. It is preferred over UPDATE...JOIN in some scenarios because it does not choose a row if multiple rows qualify for the update and an error will be thrown. MERGE is ANSI-compliant as well:
DECLARE @m AS TABLE ( a INT, b INT ) ;
DECLARE @n AS TABLE ( c INT, d INT ) ;
INSERT INTO @m SELECT 1,2 UNION ALL SELECT 2,3 ;
INSERT INTO @n SELECT 1,0 UNION ALL SELECT 2,5 ;
SELECT 'm', * FROM @m ;
SELECT 'n', * FROM @n ;
MERGE @m AS target
USING
(
SELECT c,
d
FROM @n
) AS source ( c, d )
ON ( target.a = source.c )
WHEN MATCHED AND source.d > 0
AND target.b = 3
THEN UPDATE
SET b = SOURCE.d ;
SELECT 'm', * FROM @m ;
GO
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 19, 2011 at 12:13 pm
Hi,
Thank you for your responses. I guess the final answer is... the code is efficient as it is going to be. I thought about JOINS, but it is a bit like using a sledgehammer to crack a nut.
Matt
June 19, 2011 at 12:46 pm
Sometimes a sledgehammer is the only way to go depending on the size of the nut you have to crack (or expect to have to crack down the line) 🙂
Here is one other option for you to consider.
DECLARE @m AS TABLE ( a INT, b INT ) ;
DECLARE @n AS TABLE ( c INT, d INT ) ;
INSERT INTO @m SELECT 1,2 UNION ALL SELECT 2,3 ;
INSERT INTO @n SELECT 1,0 UNION ALL SELECT 2,5 ;
SELECT 'm', * FROM @m ;
SELECT 'n', * FROM @n ;
WITH cte
AS (
SELECT m.a,
m.b,
n.d
FROM @m m
JOIN @n n ON m.a = n.c
WHERE b = 3
AND d > 0
)
UPDATE cte
SET b = d ;
SELECT 'm', * FROM @m ;
If you have not used CTEs before it may look a little odd, but I have been using them for a while and to me this looks a little cleaner mainly because it only specifies the join once. An added benefit is that you can highlight the SELECT statement in the CTE and run it to see which rows will be updated before running the select.
For large datasets the CTE option above and the UPDATE...JOIN option (they both produced the same actual execution plan on my machine) will outperform the subquery option you originally provided.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 19, 2011 at 6:41 pm
m.dunster (6/19/2011)
Hi,Thank you for your responses. I guess the final answer is... the code is efficient as it is going to be. I thought about JOINS, but it is a bit like using a sledgehammer to crack a nut.
Matt
Actually, it's your code that resembles the sledgehammer and, no, your code is certainly not as "efficient as it is going to be". 😉 Your code will use twice the resources of the proper join demonstrated by opc.three. Look at the Actual Execution Plans and see.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply