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.
| Author |
Topic |
|
Hommer
Aged Yak Warrior
808 Posts |
 Posted - 2004-10-18 : 11:43:34
|
| I am converting an application from using Sybase to SQL2k. There are a quite few of these Update statements in the syntax of:Update tbl1 inner join tbl2 on tbl1.IDcol=tbl2.FIDcolset tbl1.fld1='var1'tbl2.fld2='var2'where...I initially didn't think this could cause trouble in the SQL Server world, but it did.I mocked up some data and verified that. I want to run this through to the experts in this forum before I dive in to break up those Updates.Thanks! |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2004-10-18 : 11:51:55
|
like this?Update t1 set fld1='var1'from tbl1 t1inner join tbl2 t2 on t1.IDcol=t2.FIDcolUpdate t2 set fld2='var2'from tbl2 t2inner join tbl1 t1 on t1.IDcol=t2.FIDcolGo with the flow & have fun! Else fight the flow  |
 |
|
|
Hommer
Aged Yak Warrior
808 Posts |
Posted - 2004-10-18 : 11:57:20
|
| Yeah. But the embedded sql statements are in VB6, Access (passthrough using ADO, and native DAO) and ColdFusion. Sorting them out could cause a liitle headache. |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2004-10-18 : 12:02:10
|
| well you can upadate only one table per update...Go with the flow & have fun! Else fight the flow |
|
|
|
Hommer
Aged Yak Warrior
808 Posts |
Posted - 2004-10-18 : 12:23:08
|
| Just curious why.Those columns are uniquely identified. Wouldn’t it be more efficient to be able to update multiple tables at a time? What side affect could happen? Well, I'll just shut up and go back to work. "Fight with the flow" at another time. |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2004-10-18 : 12:59:41
|
| good question...probably to prevent silly updates....Go with the flow & have fun! Else fight the flow |
|
|
|
|
|
|
Update only work on one table