Insert values without duplicates

Question

I want to add payment type 3 to all businesses. How may i do it with MariaDB.

I do like this:

insert into business_payment_type 
        (id_payment_type, active) 
    select "4", "1" 
    from business_payment_type 
    where id_business in (select distinct(id_business) 
                          from business_payment_type)

But it return error:

1364 - Field 'id_business' doesn't have a default value

Table like on image

Answer 1

Get all of your id_business values and your new values together in one statement, then INSERT that record set into your table.

INSERT INTO business_payment_type (
   id_business
  ,id_payment_type
  ,active
  )
SELECT DISTINCT
  id_business
  ,4 AS id_payment_type
  ,1 AS active
FROM business_payment_type;