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

Question

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: 1004504 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_TOTALInt DEFAULT 0,

FEB_TOTALInt DEFAULT 0,

MAR_TOTALInt DEFAULT 0,

APR_TOTALInt DEFAULT 0,

MAY_TOTALInt DEFAULT 0,

JUN_TOTALInt DEFAULT 0,

JUL_TOTALInt DEFAULT 0,

AUG_TOTALInt DEFAULT 0,

SEP_TOTALInt DEFAULT 0,

OCT_TOTALInt DEFAULT 0,

NOV_TOTALInt DEFAULT 0,

DEC_TOTALInt 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: 1004318 More actions · Answer 3

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

http://www.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)

Ed B Hall of Fame Points: 3665 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

FROMdownwards_table

GROUPBY 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.