none
help on update Statement on SAME Rows

    Question

  • I have table TblLandRiverSegment

      

    RiverSegment          CbSegId   

    EL0_4591_0000       80             

    EL0_4591_0000        81            

    XYZ_5570_0017        91           

    EL1_5570_0001        91

    EL1_5570_0002       NULL
    EL1_5570_0002        73

     I am trying to update with help of self join
    UPDATE  T
        SET T.CbsegId =S.CbsegId
        FROM TblLandRiverSegment T
        JOIN  TblLandRiverSegment S
        ON S.RiverSegment = T.RiverSegment

    for your ease

    CREATE TABLE TestSegment
      ( RiverSegment  Varchar(100) ,    CbSegId INT )
     
    INSERT INTO   TestSegment
    VALUES('EL0_4591_0000', 80),
    ('EL0_4591_0000',81)



        UPDATE  T
        SET T.CbsegId =S.CbsegId
        FROM TestSegment t
        JOIN  TestSegment S
        ON S.RiverSegment = T.RiverSegment
         SELECT * FROM TestSegment

    If you look at the first 2 rows the  riversegment are same but cbsegid are different, when i update this update wont work  technically as update will not know what record to update ..it runs but will not update technically....so in this situation i need to throw error Raise error more records to update on multiple cbsegid have same landsegment some thing like that ..

    Please help me ..

    Thanks

     

    Wednesday, July 02, 2014 5:05 PM

Answers

  • Please don't rephrase your question and repost it as a new thread. It just creates confusion.

    Try this:

    declare @TestSegment TABLE (RiverSegment  Varchar(100), CbSegID INT, OrigCbSegID INT)
    DECLARE @RiverSegment VARCHAR(14), @error VARCHAR(45)
     
    SET NOCOUNT ON 
       
    INSERT INTO @TestSegment (RiverSegment, CbSegID, OrigCbSegID)
    VALUES
    ('EL0_4591_0000', 80, 80    ),('EL0_4591_0000', 81, 81),           
    ('XYZ_5570_0017', 91, 91    ),('EL1_5570_0001', 91, 91),
    ('EL1_5570_0002', NULL, NULL),('EL1_5570_0002', 73, 73)
    
    
    DECLARE Countah CURSOR FOR
     SELECT RiverSegment FROM @TestSegment
    OPEN countah
     FETCH Countah INTO @RiverSegment
    WHILE @@FETCH_STATUS <> -1
    BEGIN
     IF(SELECT COUNT(*) FROM @TestSegment WHERE RiverSegment = @RiverSegment GROUP BY RiverSegment) > 1
     BEGIN
      SET @error = 'Opps, too many IDs for ID: '+@RiverSegment
      RAISERROR(@error,16,1)
     END
     ELSE 
     BEGIN
    UPDATE  T
    SET T.CbsegId =S.CbsegId
      FROM @TestSegment t
        INNER JOIN @TestSegment S 
          ON S.RiverSegment = T.RiverSegment
    	  AND T.RiverSegment = @RiverSegment
      PRINT 'Updated ID: '+@RiverSegment
     END
     FETCH Countah INTO @RiverSegment
    END
    CLOSE Countah
    DEALLOCATE Countah
    
    SELECT * 
      FROM @TestSegment

    • Marked as answer by coolguy123SQL Thursday, July 03, 2014 2:05 PM
    Wednesday, July 02, 2014 5:22 PM
  • to throw the error you can do check like this

    IF EXISTS(SELECT 1
    FROM TblLandriverSegment
    GROUP BY riverSegment
    HAVING COUNT(DISTINCT CbSegId) > 1
    )
    RAISERROR 'Multiple Cbsegids found on same riverSegment',16,1
    
    ELSE
    ... your update


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    • Marked as answer by coolguy123SQL Thursday, July 03, 2014 2:05 PM
    Wednesday, July 02, 2014 7:12 PM

All replies

  • Please don't rephrase your question and repost it as a new thread. It just creates confusion.

    Try this:

    declare @TestSegment TABLE (RiverSegment  Varchar(100), CbSegID INT, OrigCbSegID INT)
    DECLARE @RiverSegment VARCHAR(14), @error VARCHAR(45)
     
    SET NOCOUNT ON 
       
    INSERT INTO @TestSegment (RiverSegment, CbSegID, OrigCbSegID)
    VALUES
    ('EL0_4591_0000', 80, 80    ),('EL0_4591_0000', 81, 81),           
    ('XYZ_5570_0017', 91, 91    ),('EL1_5570_0001', 91, 91),
    ('EL1_5570_0002', NULL, NULL),('EL1_5570_0002', 73, 73)
    
    
    DECLARE Countah CURSOR FOR
     SELECT RiverSegment FROM @TestSegment
    OPEN countah
     FETCH Countah INTO @RiverSegment
    WHILE @@FETCH_STATUS <> -1
    BEGIN
     IF(SELECT COUNT(*) FROM @TestSegment WHERE RiverSegment = @RiverSegment GROUP BY RiverSegment) > 1
     BEGIN
      SET @error = 'Opps, too many IDs for ID: '+@RiverSegment
      RAISERROR(@error,16,1)
     END
     ELSE 
     BEGIN
    UPDATE  T
    SET T.CbsegId =S.CbsegId
      FROM @TestSegment t
        INNER JOIN @TestSegment S 
          ON S.RiverSegment = T.RiverSegment
    	  AND T.RiverSegment = @RiverSegment
      PRINT 'Updated ID: '+@RiverSegment
     END
     FETCH Countah INTO @RiverSegment
    END
    CLOSE Countah
    DEALLOCATE Countah
    
    SELECT * 
      FROM @TestSegment

    • Marked as answer by coolguy123SQL Thursday, July 03, 2014 2:05 PM
    Wednesday, July 02, 2014 5:22 PM
  • more details ..

    select * from  TblLandRiverSegment
    where SUBSTRING(landriversegment,7,19)='PL7_4980_0000'

    LrsegId         RiverSegment      FIPS               CbsegId    OutOfCBWS
    2319             PL7_4980_0000    51510         16    0
    2320             PL7_4980_0000    51510         16    0
    2323             PL7_4980_0000    24033         70    0
    2324            PL7_4980_0000    24033          70    0    
        

    --this update wont work 
        UPDATE  T
        SET T.CbsegId =S.CbsegId
        FROM TblLandRiverSegment T
        JOIN  TblLandRiverSegment S
        ON S.RiverSegment = T.RiverSegment

    i want here an error message says 2 different cbsegids have  on same riversegments


     

    Wednesday, July 02, 2014 5:23 PM
  • This is probably a modeling problem, not a logic problem. 

    David


    David http://blogs.msdn.com/b/dbrowne/

    Wednesday, July 02, 2014 5:34 PM
  • Does the previous solution not solve this?
    Wednesday, July 02, 2014 5:35 PM
  • No Sir ...anyways thanks for the help....
    Wednesday, July 02, 2014 5:38 PM
  • David, Thanks for your time and i initially thought the same but It is not a Modelling problem ...there are some situations we will have data like that
    Wednesday, July 02, 2014 5:39 PM
  • Can you explain how it doesn't?

    When I tested the solution it seemed to do what you needed.

    Wednesday, July 02, 2014 5:40 PM
  • to throw the error you can do check like this

    IF EXISTS(SELECT 1
    FROM TblLandriverSegment
    GROUP BY riverSegment
    HAVING COUNT(DISTINCT CbSegId) > 1
    )
    RAISERROR 'Multiple Cbsegids found on same riverSegment',16,1
    
    ELSE
    ... your update


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    • Marked as answer by coolguy123SQL Thursday, July 03, 2014 2:05 PM
    Wednesday, July 02, 2014 7:12 PM
  • Another better option would be to eliminate the duplicate by updating all records with one of the segids (either max of min) like below

    UPDATE t
    SET CbsegId = MaxSegID
    FROM (SELECT CbSegId, MAX(CbSegId) OVER (PARTITION BY RiverSegment) AS MaxSegID
    FROM TblLandriverSegment)t


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Wednesday, July 02, 2014 7:14 PM
  • That's exactly what he wants to avoid.. He does not want to perform any updates on the data with duplicated IDs...
    Wednesday, July 02, 2014 7:46 PM
  • more details ..

    select * from  TblLandRiverSegment
    where SUBSTRING(landriversegment,7,19)='PL7_4980_0000'

    LrsegId         RiverSegment      FIPS               CbsegId    OutOfCBWS
    2319             PL7_4980_0000    51510         16    0
    2320             PL7_4980_0000    51510         16    0
    2323             PL7_4980_0000    24033         70    0
    2324            PL7_4980_0000    24033          70    0    
        

    --this update wont work 
        UPDATE  T
        SET T.CbsegId =S.CbsegId
        FROM TblLandRiverSegment T
        JOIN  TblLandRiverSegment S
        ON S.RiverSegment = T.RiverSegment

    i want here an error message says 2 different cbsegids have  on same riversegments


     

    What is it that you are trying to do?  Since you are updating a table from itself, if for a given RiverSegment value you have only one CbsegId value, you are setting the CbsegID value to itself.  That is, the update does nothing.  And if you have a RiverSegment you have multiple CbsegID values, you say you want to generate an error.  So I don't know what you are trying to do.  If you want to generate that's easy, just use Visakh16's IF EXISTS test.  But I'm don't understand at all what you want if there are no duplicates.

    Tom

    Thursday, July 03, 2014 2:56 AM
  • Hello,

    Can you post the sample data with expected result? It benefits us for further anlaysis.

    Regards,
    Fanny Liu


    Fanny Liu
    TechNet Community Support

    Thursday, July 03, 2014 8:39 AM
  • Thanks a Ton...

    This worked for me ...

    Thursday, July 03, 2014 2:05 PM