CODEWITHSUNDEEP
mysqlsqlsql-updatesql-insert
jojo
jojo

Reputation: 134

Bulk insert or update if 2 column of table already exists MySQL

I have a relation table with no primary key:

ticket_id | type | value | code

i want to do insert and update into this table with bulk method, the condition to update is if the ticket_id and code are injected with duplicate data, then it will update the type or value record. How can i achieve this?

for example i will be receiving insert data like this:

    [
      {
         "ticket_id":"1",
         "type":"name",
         "value":"Ben",
         "code":"person_name"
      },
      {
         "ticket_id":"1",
         "type":"phone",
         "value":"0812",
         "code":"person_phone"
      },
      {
         "ticket_id":"1",
         "type":"mail",
         "value":"[email protected]",
         "code":"person_mail"
      },
      {
         "ticket_id":"2",
         "type":"name",
         "value":"Jesse",
         "code":"person_name"
      },
      {
         "ticket_id":"2",
         "type":"phone",
         "value":"8272",
         "code":"person_phone"
      },
      {
         "ticket_id":"2",
         "type":"mail",
         "value":"[email protected]",
         "code":"person_mail"
      }
   ]

then the update data:

   [
      {
         "ticket_id":"1",
         "type":"name",
         "value":"Joe",
         "code":"person_name"
      },
      {
         "ticket_id":"1",
         "type":"phone",
         "value":"9810",
         "code":"person_phone"
      },
      {
         "ticket_id":"1",
         "type":"mail",
         "value":"[email protected]",
         "code":"person_mail"
      },
      {
         "ticket_id":"2",
         "type":"name",
         "value":"Rose",
         "code":"person_name"
      },
      {
         "ticket_id":"2",
         "type":"phone",
         "value":"0992",
         "code":"person_phone"
      },
      {
         "ticket_id":"2",
         "type":"mail",
         "value":"[email protected]",
         "code":"person_mail"
      }
   ]

Upvotes: 0

Views: 534

Answers (1)

GMB
GMB

Reputation: 222482

You can use the insert ... on duplicate key syntax - as commented by RiggsFolly:

insert into mytable (ticket_id, type, value, code)
values(:ticket_id, :type, :value, :code)
on duplicate key update type = values(type), value = values(value)

For this to work, you need a unique constraint on (ticket_id, code).

Upvotes: 1

Related Questions