Update on joins - SQL Server Forums

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

SQL Server 2000 Forums

Transact-SQL (2000)

Update on joins

Author

Topic

jonasalbert20
Constraint Violating Yak Guru

300 Posts

Posted - 2005-01-10 : 02:27:18

Can we be able to update on multiple tables in a single querry? HOW?

For instance i have this SQL...

SELECT BW.wWithDrawalCode,
SL.siECommerce,
BW.wRollBack,
BW.wRollBackQTY,
SL.siQuantity,
SQ.qVirtualQuantity,
SQ.qBO
FROM dbo.tblStockQueue as SQ
INNER JOIN dbo.tblStocksLocal as SL ON SQ.qQueueCode = SL.siQueueCode
RIGHT OUTER JOIN dbo.tblBranchWithdrawal as BW ON SL.siQueueCode = BW.wQueueCode

I want to update the above fields. How?

Want Philippines to become 1st World COuntry? Go for World War 3...

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2005-01-10 : 03:35:11

You will need to use multiple update statements. SQL Server does not support update of multiple tables from the same statement.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Kristen
Test

22859 Posts

Posted - 2005-01-10 : 11:32:11

I don't suppose this is a good answer to your question, but if this is to facilitate updating of this collection "as if as one table" you could:

Make a VIEW of the query
Create an INSTEAD OF TRIGGER on the View
- have the Trigger do an update on each table

Then you could just do an UPDATE on the View

Personally I would create a Stored Procedure that did the update on each table in turn, within a Transaction so that the two updates happened "as one"

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources