CODEWITHSUNDEEP
sqlsql-server
user1535268
user1535268

Reputation: 131

Create SQL query to insert a value if another one already exists

I have 3 tables. PositionCode, PositionCodeDetail, and Services

Service has serviceid, desc and status

PositionCode has codeid, positioncode, desc, status

PositionCodeDetail has positioncodedetailid, serviceid, and codeid

PositionCodeDetail is basically a list of potential services for a given position code. I now want to add a new serviceid to each position code if it has an existing service code of 15.

Basically every time a position code has serviceid 15 I want to insert a new record that would have the same codeid and add serviceid of 20. Positioncodedetailid is automatically assigned.

Any help would be appreciated.

Upvotes: 1

Views: 84

Answers (1)

Andomar
Andomar

Reputation: 238086

insert  PositionCodeDetail 
        (serviceid, codeid)
select  20
,       codeid
from    PositionCodeDetail
where   serviceid = 15

With an additional check for an existing row with service = 20:

insert  PositionCodeDetail 
        (serviceid, codeid)
select  20
,       codeid
from    PositionCodeDetail pcd1
where   serviceid = 15
        and not exists
        (
        select  *
        from    PositionCodeDetail pcd2
        where   pcd1.codeid = pcd2.codeid
                and pcd2.serviceid = 20
        )

Upvotes: 1

Related Questions