CODEWITHSUNDEEP
mysqlif-statementinsertexists
Niels Aulman
Niels Aulman

Reputation: 79

IF EXISTS THEN ELSE

It is pretty straight forward, but I could not find an answer. If the combination does exist, then UPDATE, else INSERT. 

IF EXISTS(SELECT customer_id FROM payment_conditions WHERE customer_id = 2 AND shipping_company_id = 1) 

THEN
UPDATE payment_conditions SET payment_condition = "pay in full" WHERE customer_id = 2  AND shipping_company_id = 1;

ELSE
INSERT INTO payment_conditions customer_id, shipping_company_id, payment_condition, active VALUES (2,1,"some value",1);


END IF

Upvotes: 1

Views: 3169

Answers (5)

James
James

Reputation: 38

Use the merge statement in SQL Server - it looks like these answers imply MySQL syntax. You don't state the database flavor, but Merge is the recommended way to do this on SQL Server.

Upvotes: 0

F.P
F.P

Reputation: 17831

You should set customer_id and shipping_company_id to be foreign keys, so you can use

INSERT INTO payment_conditions
    customer_id, shipping_company_id, payment_condition, active
VALUES (2, 1, "some value", 1)
ON DUPLICATE KEY UPDATE payment_condition = "pay in full"

Upvotes: 2

davek
davek

Reputation: 22925

Or issue two statements (N.B. with UPDATE first), and do away with the conditional logic. The UPDATE will have no effect if the row does not exist. This should work if your key happens to be different to the customer_id + shipping_company.

UPDATE payment_conditions SET payment_condition = "pay in full" 
WHERE customer_id = 2  AND shipping_company_id = 1;

then 

INSERT INTO payment_conditions 
(customer_id
, shipping_company_id
, payment_condition
, active) 
VALUES (2,1,"some value",1)
where not exists 
(select * FROM payment_conditions 
   WHERE customer_id = 2 AND shipping_company_id = 1);

Upvotes: 1

juergen d
juergen d

Reputation: 204894

INSERT INTO payment_conditions (customer_id, shipping_company_id, payment_condition, active) 
VALUES (2,1,"some value",1)
ON DUPLICATE KEY UPDATE payment_condition = "pay in full"

Upvotes: 1

Puggan Se
Puggan Se

Reputation: 5846

if you have a unique key on (shipping_company_id, customer_id) you can use:

INSERT INTO payment_conditions 
SET customer_id = 2,
  shipping_company_id = 1,
  payment_condition = 'some value',
  active = 1
ON DUPLICATE KEY UPDATE payment_condition = 'pay in full'

Upvotes: 2

Related Questions