Update statement, embedded select statement...

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

Other Forums

MS Access

Update statement, embedded select statement...

Author

Topic

benji012e
Starting Member

12 Posts

Posted - 2006-05-14 : 23:20:27

Hi all,

I was trying to perform an update statement with some new values and one value copied from a different row in the same table. From what I found on general SQL searches, I could do something like:

UPDATE Customers Set CustName=(Select CustName from Customers where CustID=21)

Or something to that effect. After a lot of toying, I wasn't able to make this work in Access 2003. After searching this forum, I found a solution that works perfectly. My statement is:

UPDATE Schools, People SET People.LastName = Schools.[School Name] WHERE People.FirstName='Test' and Schools.[School ID]=1

To summarize, instead of a subquery, both tables are listed in the UPDATE clause and I can just pick fields directly, qualifying fields by tablename.

Questions:

1) Will this syntax work in other SQL platforms other than Access?

2) What is this syntax called? The other was a "subquery," so does this have a name?

3) Is either technique acceptable? Is one preferable to the other? Why choose one over the other? What are some circumstances where one outshines the other? Does either have features the other doesn't? I guess I want to make sure I'm not doing something incorrectly or using SQL ineffeciently.

Thanks for any help, guys...

-Ben

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-05-16 : 04:03:58

it's called a join

yes it will work in mssql but it's a different syntax

quote:
from BOL
UPDATE titles
SET price = price * 2
FROM titles INNER JOIN publishers ON titles.pub_id = publishers.pub_id
AND pub_name = 'New Moon Books'

--------------------
keeping it simple...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-05-17 : 07:35:53

The same can be used in Access as

UPDATE titles FROM titles INNER JOIN publishers ON titles.pub_id = publishers.pub_id
SET price = price * 2 Where pub_name = 'New Moon Books'

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources