SQLTeam.com | Weblogs | Forums

Link Table to Self and updating

Having an issue with UPS Worldship exporting shipments. It won't apply my reference numbers to all packages unless I manually check a box for each RefNo. I cannot check the boxes by import switches either. So, I'm trying to update the blank cells in rows that are missing data. I've uploaded a screenshot showing the populated cells in the main tracking row and the blank cells. The code below gives me an error: The multi-part identifier "UPSShipments_1.Ref1" could not be bound.

Originally, I had no "" brackets, then added them. No joy.

What am I doing wrong?

UPDATE	[UPSShipments]
SET		[UPSShipments_1].[Ref1] = [UPSShipments].[Ref1]
FROM    [UPSShipments] AS [UPSShipments_1] INNER JOIN
		[UPSShipments] ON [UPSShipments_1].[TrackNo] = [UPSShipments].[TrackNo]
WHERE	([UPSShipments].[TrackNo] = '1Z296FA80373481000') AND (NOT ([UPSShipments].[Ref1] LIKE 'ISNULL'))

S1

Thanks!

try this in pre prod

UPDATE	tgt
SET		tgt.[Ref1] = src.[Ref1]
--select *    --I run this first just to be on the safe side.
FROM    [UPSShipments] tgt 
join UPSShipments src on src.TrackNo = tgt.TrackNo
WHERE	(src.[TrackNo] = '1Z296FA80373481000') 
   AND tgt.Ref1 IS NULL and src.Ref1 IS NOT NULL

Like a breath of fresh air! Works great!

Thanks!