How to access a current row particular column value

Question

Post reply

How to access a current row particular column value

surya-434952

SSC Eights!

Points: 952
More actions
December 18, 2009 at 10:55 am

#385973

Here is a query to update a table
update table
set col1=value
set col2=col1+some value
here col1 value in setting col2 is not getting updated value
How to get that one
Thanks in advance
Surya

Viewing 10 posts - 1 through 9 (of 9 total)

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

Ray K SSC-Dedicated Points: 31163 More actions · Answer 1

I think you'd be better off doing it like this:

update table

set col1=value, col2=value+some value

Note: the "value" in col2 is (hopefully, obviously) the same one that you put into col1.

+--------------------------------------------------------------------------------------+
Check out my blog at https://pianorayk.wordpress.com/

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 2

Provided that "value" and "some value" are different in your example, this is easy...

DECLARE @Col1 somedatatype

UPDATE yourtable

SET @Col1 = Col1 = value,

Col2 = @Col1 + someothervalue

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

surya-434952 SSC Eights! Points: 952 More actions · Answer 3

Hi Jeff,

Let me know explain the scenario clearly,

i have a table called balance sheet as follows

Dept Acct Date credit debit opening balance ending balance

1 1 2/2/09 100 50 0.00 0.00

1 1 3/2/09 150 120 0.00 0.00

1 1 4/2/09 280 120 0.00 0.00

2 1 2/2/09 300 130 0.00 0.00

2 1 3/2/09 200 110 0.00 0.00

2 2 2/2/09 200 80 0.00 0.00

for the above table i need to do update for opening balance and ending balance

ending balance should be forwarded to opening balance for next month and ending balance should be sum of opening balance + credit - debit.

Could you please help me in this.

Thanks in advance

Surya

surya-434952 SSC Eights! Points: 952 More actions · Answer 4

Hi Jeff,

I am able to carry forward ending balance to opening balance of next month, but ending balance of current month is being calculated properly.

Could you please help me in this.

Thanks in advance

Surya

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 5

surya-434952 (12/20/2009)
Hi Jeff,
Let me know explain the scenario clearly,
i have a table called balance sheet as follows
Dept Acct Date credit debit opening balance ending balance
1 1 2/2/09 100 50 0.00 0.00
1 1 3/2/09 150 120 0.00 0.00
1 1 4/2/09 280 120 0.00 0.00
2 1 2/2/09 300 130 0.00 0.00
2 1 3/2/09 200 110 0.00 0.00
2 2 2/2/09 200 80 0.00 0.00
for the above table i need to do update for opening balance and ending balance
ending balance should be forwarded to opening balance for next month and ending balance should be sum of opening balance + credit - debit.
Could you please help me in this.
Thanks in advance
Surya

Heh... That's a bit different that the original post. 😉 Also, since you're still a bit new on this forum, let me tell you the secret to getting your posts answered much more quickly and with tested code. Read and heed the article at the first link in my signature line below. People will trip over each other trying to answer your question if you post table structure and data in the way the article teaches you. You'll also get fully tested answers instead of being referred to an article or getting just verbal answers.

With that in mind, look how I built and populated the example table in the code that follows which also contains the answer to your problem. Do note that the correctly ordered clustered index, the TabLockX, and the MaxDop settings are absolutely essential,

--===== Conditionally drop the test table to make reruns easier

IF OBJECT_ID('TempDB..#BalanceSheet','U') IS NOT NULL

DROP TABLE #BalanceSheet

--===== Create and populate the test table with data from the post.

CREATE TABLE #BalanceSheet

(

Dept INTEGER,

Acct INTEGER,

Date SMALLDATETIME,

Credit DECIMAL(9,2),

Debit DECIMAL(9,2),

OpeningBalance DECIMAL(9,2),

EndingBalance DECIMAL(9,2)

)

INSERT INTO #BalanceSheet

(Dept,Acct,Date,Credit,Debit,OpeningBalance,EndingBalance)

SELECT '1','1','2/2/09','100','50' ,'0.00','0.00' UNION ALL

SELECT '1','1','3/2/09','150','120','0.00','0.00' UNION ALL

SELECT '1','1','4/2/09','280','120','0.00','0.00' UNION ALL

SELECT '2','1','2/2/09','300','130','0.00','0.00' UNION ALL

SELECT '2','1','3/2/09','200','110','0.00','0.00' UNION ALL

SELECT '2','2','2/2/09','200','80' ,'0.00','0.00'

--===== Add the required "secret ingredient" (absolutely essential)

CREATE CLUSTERED INDEX IC_#BalanceSheet_Dept_Acct_Date

ON #BalanceSheet (Dept,Acct,Date)

--===== Declare the necessary obviously named local variables

DECLARE @PrevDept INTEGER,

@PrevAcct INTEGER,

@PrevBal DECIMAL(9,2),

@Dummy DECIMAL(9,2)

--===== Update the Balance Sheet using the "Quirky Update"

-- Pseudo-Cursor method.

UPDATE #BalanceSheet

SET @Dummy = OpeningBalance = CASE

WHEN Dept = @PrevDept

AND Acct = @PrevAcct

THEN @PrevBal

ELSE 0

END,

@PrevBal = EndingBalance = CASE

WHEN Dept = @PrevDept

AND Acct = @PrevAcct

THEN @PrevBal + Credit - Debit

ELSE Credit - Debit

END,

@PrevDept = Dept,

@PrevAcct = Acct

FROM #BalanceSheet WITH (TABLOCKX)

OPTION (MAXDOP 1)

--===== Display the results

SELECT *

FROM #BalanceSheet

ORDER BY Dept, Acct, Date

This "Quirky Update" method has dozens of other uses, as well. Please see the following article for more information on it...

http://www.sqlservercentral.com/articles/T-SQL/68467/

... and don't forget to read the article at the first link in my signature line below. It WILL make life easier for you and everyone else.

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

surya-434952 SSC Eights! Points: 952 More actions · Answer 6

Hi Jeff,

Thanks a ton for your kind help. Its working. Could you please explain a bit about quirky update So that i can reuse this when ever i need to handle these kind of issues

Once again Thanks for your kind help.

Surya

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 7

surya-434952 (12/20/2009)
Hi Jeff,
Thanks a ton for your kind help. Its working. Could you please explain a bit about quirky update So that i can reuse this when ever i need to handle these kind of issues
Once again Thanks for your kind help.
Surya

I gave you a link to a whole article on the subject. 😉

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

surya-434952 SSC Eights! Points: 952 More actions · Answer 8

Hi Jeff,

Yeah i found the link. Once again thanks for your kind help.

Thanks,

Surya

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 9

You bet. Thank you for the feedback.:-)

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