UPDATE ambiguous column name - 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

General SQL Server Forums

New to SQL Server Programming

UPDATE ambiguous column name

Author

Topic

ingineu
Yak Posting Veteran

89 Posts

Posted - 2005-10-21 : 15:00:53

I'm running into problems with ambiguous column names. TransID also exists in tblWork. I tried adding SHPD + WORK, but it won't accept the SHPD on the UPDATE line. How do I code around this error? Thanks.

UPDATE tblShipmentDet SHPD 
SET
	SHPD.Quantity = WORK.Quantity
FROM tblWork WORK 
WHERE TransID = WORK.RecordID

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2005-10-21 : 15:04:09

UPDATE tblShipmentDet
SET
SHPD.Quantity = WORK.Quantity
FROM tblShipmentDet SHPD
INNER JOIN tblWork WORK
ON SHPD.TransID = WORK.RecordID

Duane.

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2005-10-21 : 15:04:26

UPDATE SHPD
SET SHPD.Quantity = WORK.Quantity
FROM tblShipmentDet SHPD
JOIN tblWork WORK ON ....

Damn you Ditch!

again!

---------------------------
EmeraldCityDomains.com

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2005-10-21 : 15:05:54

Actually, Ditch, don't you have to use the alias on the UPDATE line?

---------------------------
EmeraldCityDomains.com

ingineu
Yak Posting Veteran

89 Posts

Posted - 2005-10-21 : 15:13:27

Wow ... that was quick. UPDATE SHPD worked. Does this not appear to be inefficient? My tblWork field contains maximum of 5 records, whereas the tblShipmentDet file is in the 100,000's.

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2005-10-21 : 15:20:58

Inefficient how? You're joining two tables together and if they are properly indexed, that's a pretty quick thing to do. Were you thinking it would be faster to do some sort of subquery? I don't think it would. What other method of updating the rows did you have in mind?

---------------------------
EmeraldCityDomains.com

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2005-10-21 : 15:24:39

quote:
Originally posted by AjarnMark

Actually, Ditch, don't you have to use the alias on the UPDATE line?

---------------------------

yip - you probably do - admittedly I didn't test the code - I thought I knew the exact syntax

Duane.

ingineu
Yak Posting Veteran

89 Posts

Posted - 2005-10-21 : 15:37:41

I just thought if you say FROM tblWork, it would only pickup 5 records, whereas now we are looking at tblShipmentDet as the primary table.

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2005-10-21 : 16:27:44

Behind the scenes, the query optimizer will pick the most efficient way to join them together, which may not necessarily be the order you have them listed in your FROM clause. It will then filter down to the appropriate set (5 records if there is a 1:1 relationship) and process the update on those. Your indexing can affect how rapidly that occurs. But logically, regardless of how you state it, it still has to find the records in tblShipmentDet that match the ones in tblWork one way or another.

---------------------------
EmeraldCityDomains.com

ingineu
Yak Posting Veteran

89 Posts

Posted - 2005-10-21 : 17:35:00

Thanks .. makes sense.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources