Updating a field if not already in table 1

[prismtx]

I am trying to update a field from a value of 10 to a value of 20, unless a record for that City already is in the table with that value. I can do a straight update, but then I might get 2 records that are identical. I don't know how to code sql to do this.

select * from zone z
   set type_seq = 20
where S_NAME in ('AAA', 'BBB', 'CCC')
   and type_seq = 10

If 'BBB' has 2 records, one with 10 and one with 20, I want to skip it and not make 2 records with a type of 20. So I need a way to exclude S_NAME = 'BBB' if a record already exists with a type of 20. This is not part of a key, so it does not give an error if 2 records have the same S_NAME and type value.

 

[SantaMufasa]

Prismtx,

There are several methods to accomplish what you want. Here are some sample data:

select * from zone;

S_NAME                 TYPE_SEQ
-------------------- ----------
AAA                          10
BBB                          10
BBB                          20
CCC                          10
DDD                          10

With these data, two rows should update from your original scenario ('AAA' and 'CCC').

Here is some UPDATE code that should do what you want:

update zone x 
   set type_seq = 20
 where not exists (select 'prism' from zone y
                    where x.s_name = y.s_name
                      and y.type_seq = 20)
   and x.s_name in ('AAA', 'BBB', 'CCC')
   and x.type_seq = 10
/

2 rows updated.

SQL> select * from zone;

S_NAME                 TYPE_SEQ
-------------------- ----------
AAA                          20
BBB                          10
BBB                          20
CCC                          20
DDD                          10

5 rows selected.

Let us know if this resolves your need or if you have questions.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[prismtx]

That worked like a charm! Thanks SantaMufasa!