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
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
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'
)
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'
);
