How to add conditional insert statements in PLSQL-CodePudding

I have a query like this

insert into states(state_name,cust_id)
select 'Sleeping',customer.cust_id from customer;

I have a states table and customer table, for each customer i want to insert a row in the states table with state as 'Sleeping'.

Lets say i have three rows in the customer table , the above query will insert three rows in the states table with corresponding cust_id's from the customer table.

Now , In this query i just want to make sure that i insert only if that state is not existing in the table. I am currently stuck here and not sure how i put that check here in this query . Can anyone help me ?

CodePudding user response：

Use merge instead:

merge into states s
  using customer c
  on (    c.cust_id = s.cust_id
      and s.state_name =' Sleeping')
when not matched then insert (state_name, cust_id)
  values ('Sleeping', c.cust_id);