Updating diff columns & multiple rows of same table

Question

Updating diff columns & multiple rows of same table

Joy Smith San

SSC-Insane

Points: 24877
More actions
June 9, 2009 at 8:51 am

#131039

Dear All,
I have a table in which there are 3 fields.
colA-colB-colC
Now, The value of colC = colA - colB
and value of colA = colC of row above it..
so whenever user changes the value "colA" following should happen.
1. It will change the colC of the modified row.
2. And It Will change colA & colC of all the rows below it.
I would prefer NOT TO use cursors.
How can I do it using single query..? Is it possible at first place..
Thanks in advance.

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

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

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

I really hope you have more than three columns in this table. there is no order assumed in a table, so what determines the order of the rows in the table?

It would benefit you, and us, if you could provide us with the DDL for the table, sample data (in a readily consummable format that can be cut, paste, and executed in SSMS to load the table), expected results based on the sample data. In this case, you would also want to provide additional sample data that would be inserted/updated after the initial load plus the expected results based on those inserts/updates.

Please read the first article I reference below in my signature block for more info on posting questions to get the best help.

Tom257 SSChampion Points: 11543 More actions · Answer 2

Not exactly there yet, without some sample data. It relies on having a primary key, pKey, in numerical sequence

UPDATE T1

SET T1.ColC = T1.ColA - T1.ColB,

T1.ColA = T2.ColC

FROM Tab1 AS T1

JOIN Tab1 AS T2

ON T1.PKey = T2.pKey + 1

You'll probably want to experiment with something along these lines.

Joy Smith San SSC-Insane Points: 24877 More actions · Answer 3

UPDATE T1
SET T1.ColC = T1.ColA - T1.ColB,
T1.ColA = T2.ColC
FROM Tab1 AS T1
JOIN Tab1 AS T2
ON T1.PKey = T2.pKey + 1

This won't do. I had tried it before posting in forum.

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

San (6/9/2009)
UPDATE T1
SET T1.ColC = T1.ColA - T1.ColB,
T1.ColA = T2.ColC
FROM Tab1 AS T1
JOIN Tab1 AS T2
ON T1.PKey = T2.pKey + 1
This won't do. I had tried it before posting in forum.

Do the following:

1. Read the first article I reference below in my signature block regarding asking for assistance.

2. Post the DDL for your table, including indexes

3. Post sample data in a readily consummable format (per the article in #1)

4. Post the expected results based on the sample data provided in #3

This will get you the best possible answer with tested code.

Edit:

Oh, and 5) I may have a solution, but I need to see your problem first.

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

Still waiting. We are here to help, but we need your assistance to do that.

Jeff Moden SSC Guru Points: 1004378 More actions · Answer 6

{insert jeaopardy them song here} Heh... I'll just bet someone talked him into a cursor solution.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Joy Smith San SSC-Insane Points: 24877 More actions · Answer 7

Dear All,

Sorry for not replying. Am at client's office sine 2 days.

Cant open the site there. will go back to my office on monday and will post all the required data.

I dont want use cursor...will not use it.

Once again sorry for delay.Thank you somuch for youre response.