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 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 [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.