Mass update with record creation - Help with SQL query

Workflows
,
1

Hi everyone,

I'm relatively new to SQL queries and database best practices. I'm looking to achieve a mass update for items in my database based on their catalogitemid. Here's the scenario:

  • If a record with the matching catalogitemid exists, I want to update it.
  • If the catalogitemid doesn't exist, I want to create a new record.

I have the data prepared in an array of objects, like this:

[
  {
    "catalogitemdescription" : "Day SPF30 & Night Eye Cream Duo 1.0 oz",
    "catalogitemid" : "e5cc33b2-92d6-4cd3-9f5d-ee140ccf338a",
    "cost" : "23.00",
    "costmultiplier" : "",
    "costperunit" : "23.00",
    "created_at" : "2024-04-07T23:01:18.362Z",
    "currentcontractcost" : "0.00",
    "itemgroup" : "",
    "labeltypetext" : "Generic",
    "legacysupplieritemnumber" : "",
    "manufacturer" : "",
    "minimumordersize" : "",
    "modified_at" : "",
    "msrp" : "46.00",
    "msrpmultiplier" : "",
    "ndc" : "",
    "rebatecost" : "",
    "rebatecostmultiplier" : "",
    "rebatecostperunit" : "",
    "retailpricedivisor" : "",
    "shippingsize" : " (1) 1.00 EA",
    "statustypetext" : "Available",
    "supplieritemnumber" : "CL10428",
    "suppliername" : "Coola",
    "supplierretailprice" : "46.00",
    "upc" : "850008614200"
  },
.....{more records here..}
]

Additionally, I'd like to set add a modified_at value whenever a record is updated.

Any guidance on how to approach this with an SQL query would be greatly appreciated!