sqloracle-databasesql-updateunique-constraintora-00001

SQL: Update registries generates Constraint violation

I have a table like this:

Table

What i want to do is delete the first letter in Username column if this is an 'U'.

I have a problem if i do:

UPDATE table
     SET USERNAME= SUBSTR(USERNAME, 2, LENGTH(USERNAME))
     WHERE USERNAME LIKE 'U%';

ORA-00001: unique constraint (SPE.UQ_P_USUARIO_NOMBRE) violated.

As you can see in the example, in the table exist the registry 1111-A, and when U1111-A is modifying the error appears.

I want to generate a query which modify just the registries that dont exist in the table.What can i do?

Solution

  • If what you want is to update just the rows in which your update won't violate unique constraint you can use this 

    UPDATE `table`
SET USERNAME= SUBSTR(USERNAME, 2, LENGTH(USERNAME))
WHERE USERNAME LIKE 'U%'
AND NOT EXISTS(SELECT 1 FROM `table` t WHERE t.USERNAME = SUBSTR(`table`.USERNAME, 2, LENGTH(`table`.USERNAME)) AND t.NAME = `table`.NAME);

    This line checks if any rows with username-name pair you are trying to create already exist and if they do this row is not updated

    NOT EXISTS(SELECT 1 FROM `table` t WHERE t.USERNAME = SUBSTR(`table`.USERNAME, 2, LENGTH(`table`.USERNAME)) AND t.NAME = `table`.NAME)