June 19, 2012 at 8:40 pm
hi
i need to update 1 table based on other.
first table
declare @temp1 table
(id int,
member nvarchar(30),
value float)
insert into @temp1 values(1 , 'clinic1', 324.2)
insert into @temp1 values(1 , 'clinic2', 32.2)
insert into @temp1 values(1 , 'clinic3', 34.2)
now i have another table
declare @temp2 table
(id int,
member nvarchar(30),
value float)
insert into @temp1 values(1 , 'clinic1', 20)
insert into @temp1 values(1 , 'clinic2', 30)
insert into @temp1 values(1 , 'clinic3', 50
)
i want to something like this
update @temp2 a
set a.value = b. value from @temp1 b where a.member = b.member
or
i can insert value in @temp2 something like this
insert into @temp2
select id,
member,
value = case when c.member = b.member
then b.value
else
c.value
from @temp3 c join @temp2 b
on c.id = b.id
my prob here is i need to put where clause,otherwise it gives so many rows with different values, andi cant put where clause ,if i put it will just update those value.
any suggestion
June 19, 2012 at 11:45 pm
I'm not 100% sure of your question, but I see at least 3 issues here. First, your DDL and setup data is wrong (you're not INSERTing anything into @temp2), so let's fix it:
declare @temp1 table (id int, member nvarchar(30), value float)
insert into @temp1 values(1 , 'clinic1', 324.2)
insert into @temp1 values(1 , 'clinic2', 32.2)
insert into @temp1 values(1 , 'clinic3', 34.2)
declare @temp2 table (id int, member nvarchar(30), value float)
insert into @temp2 values(1 , 'clinic1', 20)
insert into @temp2 values(1 , 'clinic2', 30)
insert into @temp2 values(1 , 'clinic3', 50)
SELECT * FROM @temp1
SELECT * FROM @temp2
The other 2 issues have to do with your construction of the UPDATE and INSERT. To UPDATE or INSERT, as you seem to suggest you want to do, the code is as follows:
-- Update @temp1 from @temp2
UPDATE a
SET a.value = b. value
FROM @temp1 a
INNER JOIN @temp2 b ON a.member = b.member
SELECT * FROM @temp1
-- Insert into @temp2 (I've used CTE: temp3 to represent a @temp3 table variable)
;WITH temp3 (id, member, value) AS (
SELECT 1, 'clinic4', 60
)
INSERT INTO @temp2
SELECT c.id, c.member
,value = c.value
FROM temp3 c
SELECT * FROM @temp2
I'm not so sure of exactly what you're trying to do with the INSERT. If it is just to copy everything from @temp3 into @temp2, what I wrote should work for you.
If you need to put some constraints on it (e.g., member or id must already exist in @temp2 otherwise don't INSERT), you'd need to do it a little differently.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
June 19, 2012 at 11:47 pm
June 19, 2012 at 11:49 pm
June 19, 2012 at 11:57 pm
vinu512 (6/19/2012)
Oops!!!!....Sorry Dwain....didnt see your post...I guess...we were typing at the same time. 😀
No harm done. Seems we're frequently racing.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
June 20, 2012 at 12:16 am
dwain.c (6/19/2012)
vinu512 (6/19/2012)
Oops!!!!....Sorry Dwain....didnt see your post...I guess...we were typing at the same time. 😀No harm done. Seems we're frequently racing.
Yes, seems like it. Its good. 🙂
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply