CODEWITHSUNDEEP
sqlhivehiveql
Shabir Hamid
Shabir Hamid

Reputation: 148

Insert rows for records that exists with a distinct id

I'd like to insert a row for each distinct id that exists in my table. What is the best and efficient practice to add these rows for each distinct id?

ID      Name        Count
--      ----        ---
A1      ABC          4
A1      BCD          2
B1      KLM          1
C2      STU          3
C2      BCD          1
C2      DEF          5

EXPECTED RESULT AFTER INSERT

ID      Name        Count
--      ----        ---
A1      ABC          4
A1      BCD          2
A1      EXISTS       1
B1      KLM          7
B1      EXISTS       1
C2      STU          3
C2      BCD          4
C2      DEF          5
C2      EXISTS       1

Upvotes: 0

Views: 45

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270081

Use union all:

select id, name, count
from t
union all
select distinct id, 'EXISTS', 1
from t;

An insert is even simpler:

insert into t (id, name, count)
    select distinct id, 'EXISTS', 1
    from t;

Upvotes: 1

Related Questions