UPDATE statement, SubQuery involved

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

SQL Server Development (2000)

UPDATE statement, SubQuery involved

Author

Topic

benji012e
Starting Member

12 Posts

Posted - 2006-05-15 : 21:34:32

Hi all,

I posted this in the access forum but I think that may not have been the right place.

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

Kristen
Test

22859 Posts

Posted - 2006-05-16 : 02:26:27

The syntax I use is:


UPDATE P
SET People.LastName = Schools.[School Name] 
FROM Schools
     JOIN People AS P
          ON P.SomeColumn Schools.SomeColumn

but this assumes some commonality between the two tables.

Kristen

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-05-16 : 02:31:34

Slight change to Kristen's code


UPDATE P
SET People.LastName = Schools.[School Name] 
FROM Schools
     JOIN People AS P
          ON P.SomeColumn  = Schools.SomeColumn

If Debugging is the process of removing Bugs then i Guess programming should be process of Adding them.

Kristen
Test

22859 Posts

Posted - 2006-05-16 : 02:33:35

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources