Update Table Problem - 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 Table Problem

Author

Topic

bbowser
Starting Member

43 Posts

Posted - 2007-02-05 : 14:29:49

I have two tables, Table1 has columns City, State, Zip, County and Table2 is a customers table that contains columns such as fname, lname, city, state, zip, county, address1, address2 etc. I need to populate table2 with the county data from table1 using an Update function. I've used the following to no avail.

Update table1
Set county = (Select county from table2 where table1.city = table2.city)
Where Exists (Select county from table2 where table1.city = table2.city)

I end up getting the following error:
Server: Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.

I assume I'm getting this error because our cityzipcounty table has multiple returns in the county column which would have several cities for each county.
How can I populate my table2 (customers table)county column with the county from the Table1 (cityzipcounty) table?

Kristen
Test

22859 Posts

Posted - 2007-02-05 : 14:41:49

This perhaps?


UPDATE T1
SET county = T2.county
FROM table1 AS T1
     JOIN table2 AS T2
         ON T2.city = T1.city

"... with the county from the Table1 (city zip county) table?"

you may need to extend the JOIN to get a narrower match, e.g.:


FROM table1 AS T1
     JOIN table2 AS T2
         ON T2.city = T1.city
        AND T2.zip = T1.zip

Kristen

bbowser
Starting Member

43 Posts

Posted - 2007-02-05 : 15:10:18

I tried it and it didn't work.
I got this error: Server: Msg 107, Level 16, State 3, Line 1
The column prefix 'dbo.cityzipcounty' does not match with a table name or alias name used in the query.
Here's how I entered the Update statement:
Update contacts
Set county = cityzipcounty.county
From contacts As t1
Join cityzipcounty As t2
On t2.city = t1.city

I also tried option 2. Didn't work.

Kristen
Test

22859 Posts

Posted - 2007-02-05 : 15:24:46

Change

Set county = cityzipcounty.county

to

Set county = t2.county

When a table is given an Alias that should be used instead of the table name.

Make sure you have a backup! If it does the update wrongly you may want to restore and start again ...

Kristen

bbowser
Starting Member

43 Posts

Posted - 2007-02-05 : 15:32:10

Kristen, Your a genius! It looks like it worked.
Can u explain to me how the Alias table thing works?
Thank you so much.

Kristen
Test

22859 Posts

Posted - 2007-02-05 : 23:50:36

Say you have one table, but it needs to reference itself. Employees is a typical example, where there is a column for the Employee Number of the Boss:


CREATE TABLE Employee
(
   ID int NOT NULL,
   Name varchar(30),
   BossID int
)

Now, you would like to make a query of the Employee table:


SELECT ID, Name, BossID
FROM Employee

... but you would like to have the Name of the Boss instead of the ID:


SELECT ID, Name, BossID, Name
FROM Employee
     JOIN Employee
         ON BossID = ID

problem is, we now have two instances of the table Employee - and all the column references are now ambiguous (if you try this one SQL Server will tell you its ambiguous, and can't be run)

So to get around this you have to introduce aliases:


SELECT  E.ID, E.Name, E.BossID, B.Name
FROM Employee AS E
     JOIN Employee AS B
         ON E.BossID = B.ID

in an Update you can do:

UPDATE MyTable
SET ...

or

UPDATE MyTable
SET ...
JOIN MyOtherTable

but when you do

UPDATE MyTable
SET ...
FROM MyTable
JOIN MyOtherTable

I personally think it starts getting complex. So for consistency we already use an Alias for updates:

UPDATE U
SET ...
FROM MyTable AS U

for the simple case, and

UPDATE U
SET ...
FROM MyTable AS U
JOIN MyOtherTable

for the more complex ones. Thus the style is consistent across all of our code (and we use "U" as the alias name for the table being updated in 99% of cases, also for consistency)

Kristen

bbowser
Starting Member

43 Posts

Posted - 2007-02-06 : 10:53:39

Thanks again Kristen. The solution did work and thank you for the explanation. This will help a bunch.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources