Unique string prevents duplicate rows

[cms9651]

Hello guys!

I need your help.
This is my query in mysql.

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?

[Err] 1062 - Duplicate entry '60138282ARGO' for key 'UniqueString'

How can i do it?

Any help?
Thank you.

[r937]

you could use UPDATE IGNORE

[cms9651]

thank you!

[StarLion]

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.

[r937]

... will leave any duplicates set to NULL

huh?

could you please explain in a bit more detail? this is news to me

[cms9651]

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:

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;

[StarLion]

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.

[r937]

... 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