[xmodz]

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.

[Ionut]

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)

[baavgai]

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