CODEWITHSUNDEEP
sqlsql-serveroracle11g
sksallaj
sksallaj

Reputation: 4010

Writing a single UPDATE statement that prevents duplicates

I've been trying for a few hours (probably more than I needed to) to figure out the best way to write an update sql query that will dissallow duplicates on the column I am updating.

Meaning, if TableA.ColA already has a name 'TEST1', then when I'm changing another record, then I simply can't pick a value for ColA to be 'TEST1'.

It's pretty easy to simply just separate the query into a select, and use a server layer code that would allow conditional logic:

SELECT ID, NAME FROM TABLEA WHERE NAME = 'TEST1'

IF TableA.recordcount > 0 then
    UPDATE SET NAME = 'TEST1' WHERE ID = 1234
END IF

But I'm more interested to see if these two queries can be combined into a single query.

I am using Oracle to figure things out, but I'd love to see a SQL Server query as well. I figured a MERGE statement can work, but for obvious reasons you can't have the clause:

..etc.. WHEN NOT MATCHED UPDATE SET ..etc.. WHERE ID = 1234

AND you can't update a column if it's mentioned in the join (oracle limitation but not limited to SQL Server)

ALSO, I know you can put a constraint on a column that prevents duplicate values, but I'd be interested to see if there is such a query that can do this without using constraint.

Here is an example start-up attempt on my end just to see what I can come up with (explanations on it failed is not necessary):

ERROR: ORA-01732: data manipulation operation not legal on this view

UPDATE (
    SELECT d.NAME, ch.NAME FROM (
        SELECT 'test1' AS NAME, '2722' AS ID
        FROM DUAL
    ) d
    LEFT JOIN TABLEA a
    ON UPPER(a.name) = UPPER(d.name)
)
SET a.name = 'test2'
WHERE a.name is null and a.id = d.id

I have tried merge, but just gave up thinking it's not possible. I've also considered not exists (but I'd have to be careful since I might accidentally update every other record that doesn't match a criteria)

Upvotes: 0

Views: 7312

Answers (2)

Nivas
Nivas

Reputation: 18334

It should be straightforward:

update personnel
set personnel_number = 'xyz'
where person_id = 1001
and not exists (select * from personnel where personnel_number = 'xyz');

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269933

If I understand correctly, you want to conditionally update a field, assuming the value is not found. The following query does this. It should work in both SQL Server and Oracle:

update table1
    set name = 'Test1'
    where (select count(*) from table1 where name = 'Test1') > 0 and
          id = 1234

Upvotes: 1

Related Questions