Update Statement needed 2

[mtepfer]

Have one table with vendor information in it. The vendor may be in this table duplicate times, their fed id number is the same but their index is different. Want to update the index to the lower index number when the fed id number matches.

Example :

FED ID VendID
123-45-6789 8939
123-45-6789 94275

Want to update the 94275 with the 8939.

Thanks in advance for any assistance.

 

[bborissov]

UPDATE YourTable SET VendId = Tbl1.VendId
FROM YourTable
INNER JOIN (SELECT FedId, MIN(VendId) AS VendId
                   FROM YourTable
             GROUP BY FedId) Tbl1
  ON Tbl1.FedId  =  YourTable.FedId AND
     Tbl1.VendId <> YourTable.VendId

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[PWise]

Update tablename
Set VendID=mVendID
From Tablename
inner join (
            Select [FED ID] ,min(VendID) mVendID
            From tablename
            Group by  [FED ID]
            )minVend
on minVend.[FED ID]=[Tablename.FED ID]
And VendID <>mVendID

 

[PWise]

Sorry bborissov for double posting I just was not fast enough

 

[bborissov]

Better double then none

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.