Update multiple row in table with temp tbl value

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Update multiple row in table with temp tbl value

Author

Topic

dhinasql
Posting Yak Master

195 Posts

Posted - 2010-08-20 : 02:51:39

Friends,

I am having one table name "Requested" i want to update this table using temporary table value, please find my table structure and temp table values for your reference.

Table : Requested
---------------------
RequestCode|SNO|RefCode|FuncCode|ROWCode|UpdateCode|StatusCode
10901 | 1 | 70 | 1000 | 102 | 10002334 | 1
10901 | 2 | 70 | 1000 | 108 | 11900202 | 1
10901 | 3 | 71 | 1001 | 108 | 12322455 | 1
10901 | 4 | 70 | 1000 | 119 | 12002012 | 1
10902 | 1 | 70 | 1000 | 122 | 31212121 | 1
10902 | 1 | 71 | 1004 | 133 | 23232323 | 1

TempTable : #TempCompareTbl
----------------------------
TRequestCode|TRefCode|TFuncCode|TROWCode|TUpdateCode|TStatusCode
10901 | 70 | 1000 | 102 | 45444455 | 3
10901 | 70 | 1000 | 119 | 65566565 | 2

I have to compare "#TempCompareTbl" Column TRequestCode|TRefCode|TFuncCode|TROWCode in WHERE Condition with "Requested" Table Column RequestCode|RefCode|FuncCode|ROWCode|
And update What value we have in "#TempCompareTbl" Column TUpdateCode|TStatusCode TO "Requested" table Column UpdateCode|StatusCode.

Expected Output After Executing the Query is

EXPECTED OUTPUT
----------------

Table : Requested
---------------------
RequestCode|SNO|RefCode|FuncCode|ROWCode|UpdateCode|StatusCode
10901 | 1 | 70 | 1000 | 102 | 45444455 | 3
10901 | 2 | 70 | 1000 | 108 | 11900202 | 1
10901 | 3 | 71 | 1001 | 108 | 12322455 | 1
10901 | 4 | 70 | 1000 | 119 | 65566565 | 2
10902 | 1 | 70 | 1000 | 122 | 31212121 | 1
10902 | 1 | 71 | 1004 | 133 | 23232323 | 1

Please help me to get the expected output.

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-08-20 : 02:58:43

UPDATE Requested SET UpdateCode = T.TUpdateCode, StatusCode = T.TStatusCode
FROM #TempCompareTbl T
INNER JOIN Requested R
ON T.TRequestCode = R.RequestCode AND
T.TRefCode = R.RefCode AND
T.TFuncCode = R.FuncCode AND
T.TROWCode = R.ROWCode

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

dhinasql
Posting Yak Master

195 Posts

Posted - 2010-08-20 : 03:03:15

Hi Vaibhav,

Thanks for your Rapid response,

Let me try this and let you know.

Thanks Again

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources