locked
Problems with a multi-part identifier RRS feed

  • Question

  • User-98709996 posted

    Hello,

    I'm trying to concatenate data to a single table and have produced an update statement, I am getting an error on the SET part of my query stating that the multipart identifier cannot be bound for both ec.swl1 and ec.swl2.  Whilst I know what the error means I can't pinpoint where the problem is, can anyone help at all?

    Here is my query:

    UPDATE Vessel
    	SET CraneSwl1 = ec.swl1,
    	    CraneSwl2 = ec.swl2
    
    SELECT	ve.*, 
    	ec.swl1, 
    	ec.swl2
    
    FROM Vessel ve
    JOIN (SELECT de.VesselId, 
    	     MAX(cr.SWL) AS swl1, 
         	     NULLIF(MIN(cr.SWL), MAX(cr.SWL)) AS swl2	
          FROM OldDeckEquipment de 
            JOIN OldCrane cr ON de.DeckEquipmentId = cr.DeckEquipmentId 
          GROUP BY de.VesselId
          ) ec ON ec.VesselId = ve.oldId

    The subquery select statement executed as expected when it's run on its own, it's the addition of the update statement that's causing the problem.

    Wednesday, March 4, 2020 3:25 PM

All replies

  • User77042963 posted
    MERGE Vessel ve
    Using (SELECT de.VesselId, 
    	     MAX(cr.SWL) AS swl1, 
         	     NULLIF(MIN(cr.SWL), MAX(cr.SWL)) AS swl2	
          FROM OldDeckEquipment de 
            JOIN OldCrane cr ON de.DeckEquipmentId = cr.DeckEquipmentId 
          GROUP BY de.VesselId
          ) ec ON ec.VesselId = ve.oldId
     When matched then
     UPDATE  
    	SET CraneSwl1 = ec.swl1,
    	    CraneSwl2 = ec.swl2;

    Wednesday, March 4, 2020 5:22 PM
  • User-719153870 posted

    Hi FlintyHaggis,

    FlintyHaggis

    etting an error on the SET part of my query stating that the multipart identifier cannot be bound for both ec.swl1 and ec.swl2.

    ec.swl1 and ec.swl2  are collections of  multiple records in fact, while the update...set... statemen can only process one data at a time and that's why you got the error.

    According to the provided query, you want to select data from OldDeckEquipment and OldCrane and update them to Vessel. In this case, MERGE (Transact-SQL) could be a better choice.

    For detailed query code, you can refer to @limno's reply.

    Best Regard,

    Yang Shen

    Thursday, March 5, 2020 5:49 AM