Can you really only update one table in an UPDATE Statement?!?!

[crowsario]

Hi all,

I created an app that started off using Access as a backend. There are many places that I have updates to more than one table in a single statement. I am now trying to move to SQL Server 7.0, residing on a windows NT 4.0 machine. Everytime one of these statements is run SQL Server throws an exception..

Can you really only update one table in an UPDATE Statement?!?! I can't believe that Access would be able to and SQL Server not have this capability.. I'm stumped... confused.. and disappointed...

here's a small statement that wont even work:

UPDATE table1 as t1, table2 as t2
set t1.product= 'BaseBall', t2.SalesDept='Sports'
where t1.ID='2' and t1.ID= t2.Prod_ID

HELP PLEASE!

Thanks
-crow

 

[Stamey]

As far as I know, unless you use a view to join the tables, you can only update one table in one statement. When you want an updatable view, there are specific rules you must follow to create that view. See BOL.

Chris

 

[tlbroadbent]

Yes. I'm afraid its true! SQL Server can only update one table with a single Update statement. Good thing as far as I'm concerned.

The fact that Access can update two tables in one query doesn't make it advisable. In all my years working with relational databases, I had never thought to do that. By definition a SQL update query updates one base table. Access' Jet SQL violates many standards. This is just one example.

SQL Server is compliant to the standard that only one table should be updated in a single query. However, in T-SQL, unlike Jet SQL, you can have multiple statements in one query.

UPDATE table1
SET product='BaseBall'
FROM table1 t1
INNER JOIN table2 t2
t1.ID=t2.Prod_ID
WHERE t1.ID='2'

UPDATE table2
SET SalesDept='Sports'
FROM table1 t1
INNER JOIN table2 t2
t1.ID=t2.Prod_ID
WHERE t1.ID='2' Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions in the SQL Server forum. Many of the ideas apply to all forums.

 

[crowsario]

Thanks Chris and Terry... I guess i'll be changing a bunch of queries next week... oh bother...


-crow