SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Key unique string prevents duplicate rows

    Hello guys!

    I need your help.
    This is my query in mysql.
    Code:
    UPDATE `tblm`
    SET UniqueString = NULL;
    
    UPDATE `tblm`
    SET UniqueString = TRIM(
    	REPLACE (
    		CONCAT(
    			TES,
    			idNode,
    			idLINE,
    			REPLACE (LINE_NAME, '`', '')
    		),
    		' ',
    		''
    	)
    )
    the composition of the key unique string prevents duplicate rows... the problem is that occurs when double row the query is stopped and error in output... would be possible in case double row delete last double row and not stopped the update query?
    Code:
    [Err] 1062 - Duplicate entry '60138282ARGO' for key 'UniqueString'
    How can i do it?

    Any help?
    Thank you.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    you could use UPDATE IGNORE
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    thank you!

  4. #4
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    73 Post(s)
    Tagged
    0 Thread(s)
    It's worth pointing out that UPDATE IGNORE, while it will not stop the update from continuing, will leave any duplicates set to NULL, not try to re-run the row.

    Incidentally, is there not a natural key to the table? Seems like a little bit of an odd system to be doing to me.
    Never grow up. The instant you do, you lose all ability to imagine great things, for fear of reality crashing in.

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by StarLion View Post
    ... will leave any duplicates set to NULL
    huh?

    could you please explain in a bit more detail? this is news to me
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by StarLion View Post
    It's worth pointing out that UPDATE IGNORE, while it will not stop the update from continuing, will leave any duplicates set to NULL, not try to re-run the row.

    Incidentally, is there not a natural key to the table? Seems like a little bit of an odd system to be doing to me.
    It's true...
    Solved with:
    Code:
    UPDATE `tblm`
    SET UniqueString = NULL;
    
    UPDATE `tblm`
    SET UniqueString = TRIM(
    	REPLACE (
    		CONCAT(
    			TES,
    			idNode,
    			idLINE,
    			REPLACE (LINE_NAME, '`', '')
    		),
    		' ',
    		''
    	)
    );
    
    DELETE
    FROM
    	`tblm`
    WHERE
    	UniqueString IS NULL;
    Thanks you very much for your help
    I'm really happy for your quickly answer.
    Good bye

  7. #7
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    73 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    huh?

    could you please explain in a bit more detail? this is news to me
    if UPDATE IGNORE encounters a row it cant modify (Duplicate Key), it will leave said row alone - which, in the poster's case, will leave the row's UniqueString as NULL, because he first runs a query to set all of his rows' UniqueStrings to NULL.

    Incidentally, OP, from what it looks like you're doing, you might as well just put a multi-field key on the table and not insert the duplicate rows to begin with.
    Never grow up. The instant you do, you lose all ability to imagine great things, for fear of reality crashing in.

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by StarLion View Post
    ... which, in the poster's case, will leave the row's UniqueString as NULL
    ah, i get it... in the poster's case

    your post opened up with this statement, which, by itself, it completely misleading when not viewed in the context of the poster's case --
    It's worth pointing out that UPDATE IGNORE, while it will not stop the update from continuing, will leave any duplicates set to NULL, not try to re-run the row.
    you made it sound like that's how UPDATE IGNORE is supposed to work
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •