How to update multiple column in one table from one column in another table in one statement

Question

Post reply

How to update multiple column in one table from one column in another table in one statement

John Doe-446893

Right there with Babe

Points: 714
More actions
December 18, 2007 at 11:27 pm

#62757

I am trying to update a temp table which has Id and 12 columns for 12 month. I have to update with total amount for particular Id for particular month. we have a column for the month and totalamount for each id in the table which I want to update from. Please help.

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

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

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 1

Can you please post the schema of the temp table, and the table you're updating from, as well as some sample data for both and the desired output.

Thanks

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

John Doe-446893 Right there with Babe Points: 714 More actions · Answer 2

this is the structure of table to be updated

Id Int NOT NULL,

JAN_TOTAL Int DEFAULT 0,

FEB_TOTAL Int DEFAULT 0,

MAR_TOTAL Int DEFAULT 0,

APR_TOTAL Int DEFAULT 0,

MAY_TOTAL Int DEFAULT 0,

JUN_TOTAL Int DEFAULT 0,

JUL_TOTAL Int DEFAULT 0,

AUG_TOTAL Int DEFAULT 0,

SEP_TOTAL Int DEFAULT 0,

OCT_TOTAL Int DEFAULT 0,

NOV_TOTAL Int DEFAULT 0,

DEC_TOTAL Int DEFAULT 0

this is the structure of table to update from

ID Int NOT NULL

MM int (indicates which month is it)

TotalAmount (holds total amount for the month)

I have to update first table with the data from totalamount field of second table joining on ID for the particaular month. I tried case statement did not work. I had to update 12 times for 12 months. Thank you.

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 3

Want a great answer that's actually been tested to work... quickly? Then read this, please...

http://qa.sqlservercentral.com/articles/Best+Practices/61537/

--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)
Intro to Tally Tables and Functions

Ed B Hall of Fame Points: 3130 More actions · Answer 4

Do you have to update or can you insert ?

INSERT cross_tab_table

select id,

sum(CASE WHEN MM = 1 THEN TotalAmount ELSE 0 END) as JAN_TOTAL,

sum(CASE WHEN MM = 2 THEN TotalAmount ELSE 0 END) as FEB_TOTAL,

sum(CASE WHEN MM = 3 THEN TotalAmount ELSE 0 END) as MAR_TOTAL,

sum(CASE WHEN MM = 4 THEN TotalAmount ELSE 0 END) as APR_TOTAL,

sum(CASE WHEN MM = 5 THEN TotalAmount ELSE 0 END) as MAY_TOTAL,

sum(CASE WHEN MM = 6 THEN TotalAmount ELSE 0 END) as JUN_TOTAL,

sum(CASE WHEN MM = 7 THEN TotalAmount ELSE 0 END) as JUL_TOTAL,

sum(CASE WHEN MM = 8 THEN TotalAmount ELSE 0 END) as AUG_TOTAL,

sum(CASE WHEN MM = 9 THEN TotalAmount ELSE 0 END) as SEP_TOTAL,

sum(CASE WHEN MM = 10 THEN TotalAmount ELSE 0 END) as OCT_TOTAL,

sum(CASE WHEN MM = 11 THEN TotalAmount ELSE 0 END) as NOV_TOTAL,

sum(CASE WHEN MM = 12 THEN TotalAmount ELSE 0 END) as DEC_TOTAL

FROM downwards_table

GROUP BY id

John Doe-446893 Right there with Babe Points: 714 More actions · Answer 5

John Doe-446893

Right there with Babe

Points: 714

December 21, 2007 at 9:16 am

#762174

I have to update.