2 Replies - 2574 Views - Last Post: 11 December 2012 - 06:30 PM

#1 xmodz  Icon User is offline

  • New D.I.C Head

Reputation: 4
  • View blog
  • Posts: 38
  • Joined: 07-March 10

Ambiguous column name when trying to update joined tables

Posted 10 December 2012 - 09:25 PM

Hello peoples,
I have been pondering on this issue for quite sometime and tried various methods to get around this problem I am facing right now. I have a main table that has been extended vertically (by another table) using FULL OUTER JOIN. The resulting query was satisfactory as expected, however updating the extended (the new table) proved to be quite a challenge as Ambiguous column name error prevents me from updating the 2nd table. here's my Select and update Queries so that you can evaluate what did i do wrong.

SELECT statement

SELECT  EIR.EIRID, EIR.VoyageNumber, EIR.ContainerNumber, EIR.Measurement, EIR.Consignee, EIR.DateDelivered, 
                                       EIR.ChasisID, [Return].DateReturned, [Return].ReturnLocation, [Return].ReturnRefNo, [Return].VehicleID,
                                       [Return].DriverID, [Return].Remarks
                                 FROM  EIR FULL OUTER JOIN
                                       [Return] ON EIR.EIRID = [Return].EIRID
                                WHERE  (EIR.DateDelivered IS NOT NULL)


UPDATE Statement

UPDATE [Return] 
                                  SET [EIRID] = @EIRID, [DriverID] = @DriverID, [VehicleID] = @VehicleID, [ReturnLocation] = @ReturnLocation,
                                      [DateReturned] = @DateReturned, [ReturnRefNo] = @ReturnRefNo, [Remarks] = @Remarks
                                FROM  EIR FULL OUTER JOIN
                                      [Return] ON EIR.EIRID = [Return].EIRID
                                WHERE [EIRID] = @EIRID 



I appreciate for any help that's offered, thanks in advance.

Is This A Good Question/Topic? 0
  • +

Replies To: Ambiguous column name when trying to update joined tables

#2 Ionut  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 385
  • View blog
  • Posts: 1,057
  • Joined: 17-July 10

Re: Ambiguous column name when trying to update joined tables

Posted 11 December 2012 - 02:58 PM

I think your problem is at this line
 WHERE [EIRID] = @EIRID


EIRID column is in both tables so db engine doesn't know which one it has to use it order to perform the action. There you should add [Return or EIR (As you did in the first select in where clause)
Was This Post Helpful? 0
  • +
  • -

#3 baavgai  Icon User is offline

  • Dreaming Coder
  • member icon

Reputation: 5780
  • View blog
  • Posts: 12,595
  • Joined: 16-October 07

Re: Ambiguous column name when trying to update joined tables

Posted 11 December 2012 - 06:30 PM

The ambiguous comes from WHERE [EIRID] = @EIRID. You need to prefix it.

Given that you're passing @EIRID, it's unclear why you're joining anything.
UPDATE [Return] 
	SET -- this is your key, you shouldn't be setting it [EIRID] = @EIRID, 
		[DriverID] = @DriverID,
		[VehicleID] = @VehicleID,
		[ReturnLocation] = @ReturnLocation,
		[DateReturned] = @DateReturned,
		[ReturnRefNo] = @ReturnRefNo,
		[Remarks] = @Remarks
	WHERE [EIRID] = @EIRID


Was This Post Helpful? 0
  • +
  • -

Page 1 of 1