update table

Question

update table

harri.reddy

SSCommitted

Points: 1957
More actions
June 19, 2012 at 12:14 pm

#260181

hi ,i have 1 table
Declare @temp1 table(
id int,
code char(10),
value float)
insert into @temp1 values(1 , 'f', 345.21)
insert into @temp1 values(1 , 'f', 45.21)
insert into @temp1 values(1 , 'e', 345.21)
output
1 f 345.21
1 f 45.21
1 e 345.21
now what i want to do is if there same code appears more than 1 i need add sum in same table
so my desire result would be
1 f 390.21
1 e 345.21

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

harri.reddy (6/19/2012)
hi ,i have 1 table
Declare @temp1 table(
id int,
code char(10),
value float)
insert into @temp1 values(1 , 'f', 345.21)
insert into @temp1 values(1 , 'f', 45.21)
insert into @temp1 values(1 , 'e', 345.21)
output
1 f 345.21
1 f 45.21
1 e 345.21
now what i want to do is if there same code appears more than 1 i need add sum in same table
so my desire result would be
1 f 390.21
1 e 345.21

Your select will use a sum and group by. This is a very simple query. Please lookup GROUP BY in Books Online, it should help you figure this one out.

vinu512 SSCoach Points: 15729 More actions · Answer 2

From what I understand you are trying to Update the table.

You have two entries for the Code 'f'. So, these questions come to my mind:

1. You want to update both entries for Code 'f' with the sum.

OR

2. You want to update one of them with the Sum(which one) and keep the other one as it is.

OR

3. You want to update the first entry with Sum and Delete the rest of the entries for the same Code.

Which one of the above is your Requirement??

Vinu Vijayan

For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden[/url] 😉

Dwain Camps SSC Guru Points: 86948 More actions · Answer 3

You know, when I first saw this thread I figured Lynn had nailed it.

But then Vinu, you posted these questions and I reread the OP's original statement. If your interpretation is correct, this is a truly bizarre requirements.

So I figure bizarre requirements require bizarre solutions! Here's one for #3.

Declare @temp1 table(id int, code char(10), value float)

Declare @temp2 table(id int, code char(10), value float)

insert into @temp1 values(1 , 'f', 345.21)

insert into @temp1 values(1 , 'f', 45.21)

insert into @temp1 values(1 , 'e', 345.21)

INSERT INTO @temp2

SELECT ID, code, value

FROM (

DELETE FROM @temp1

OUTPUT DELETED.id, DELETED.code, DELETED.value

WHERE ID = 1) x

INSERT INTO @temp1

SELECT ID, code, SUM(value)

FROM @temp2

GROUP BY ID, code

SELECT * FROM @temp1

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
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

vinu512 SSCoach Points: 15729 More actions · Answer 4

Very Nice Dwain.

I can swear by anything right now that we are thinking the same stuff.

I was working something like the query you posted and as soon as i open the thread to check if the OP had posted and I see that you beat me to it again. 🙂

Vinu Vijayan

For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden[/url] 😉

Dwain Camps SSC Guru Points: 86948 More actions · Answer 5

vinu512 (6/20/2012)
Very Nice Dwain.
I can swear by anything right now that we are thinking the same stuff.
I was working something like the query you posted and as soon as i open the thread to check if the OP had posted and I see that you beat me to it again. 🙂

Actually what I was trying to do wouldn't work, so I had to settle for this.

I wanted to do one INSERT back into the first table by grouping the results of the nested query (generated by OUTPUT), thus eliminating the second INSERT. Alas it wasn't to be due to a SQL restriction!

Too bad the derived table from OUTPUT can't be used for anything except to INSERT. I'd have multiple uses for that!

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

Actually, I wasn't too sure what the OP really wanted and we haven't heard anything from the OP.