CODEWITHSUNDEEP
oracle-database
Jap Evans
Jap Evans

Reputation: 1127

Oracle update query ignore primary constraint

I have a table AreaUser like below.

area   user
------------
area2  user1
area2  user2
area3  user1

(area,user) is a pk

I execute a query like below

update areatable
set user = 'user2'
where user = 'user1'

Primary key constraint error thrown for the first row update and the third row is not updated. How do i ignore the first row error and keep going with the updating the third row or how can i find if the (area,user) combination is already there in table just before updating that row.

Hope i stated the question clearly with this example. Thanks in advance for the help.

Upvotes: 1

Views: 748

Answers (1)

Ciarán
Ciarán

Reputation: 3057

You need a NOT EXISTS, E.g.

Update AreaTable
   Set User = 'user2'
 Where User = 'user1'
   And Not Exists (Select 1
                     From AreaTable CheckAreaTable
                    Where CheckAreaTable.Area = AreaTable.Area
                      And CheckAreaTable.User = 'user2')

Upvotes: 2

Related Questions