CODEWITHSUNDEEP
sqlsql-insert
Mehmet
Mehmet

Reputation: 755

sql command conditional insert

Sample table records 

objId   ; objCode  
19-1    ;   TE  
19-2    ;   TE  
19-1    ;   KKK    
19-3    ;   DA  
19-4    ;   TE

How can be inserted KKK code to objs with objId 19-2 and 19-4 by SQL command.
Two conditions are objs must have TE and must not have KKK.
I succeed similar recording by C#.
But i want to learn whether or not can be succeed by SQL

Upvotes: 0

Views: 276

Answers (3)

GMB
GMB

Reputation: 222642

You can use do an insert ... select ... with a filter on objCode = 'TE' and a not exists condition on objCode = 'KKK':

insert into mytable
select objId, 'KKK'
from mtytable t
where objCode = 'TE'
and not exists (
    select 1 from mytable t1 where t1.objId = t.objId and t1.objCode = 'KKK'
)

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1270793

You can use a subquery in insert to get the rows that you want:

insert into t (objId, objCode)
    select t.objId, 'KKK'
    from t
    where t.objCode = 'TE' and
          not exists (select 1
                      from t t2
                      where t2.objId = t.objId and t2.objCode = 'TE'
                     );

Upvotes: 0

juergen d
juergen d

Reputation: 204894

insert into your_table (objId, objCode)
select objId, 'KKK'
from your_table
group by objId
having sum(case when objCode = 'TE' then 1 end) > 0
   and sum(case when objCode = 'KKK' then 1 end) = 0

Upvotes: 3

Related Questions