if row exists update else insert

[Destramic]

hey guys im wanting  to check if a row exists then update, else insert.

 

i've been trying to get this query to work, but i'm not having much luck...i know some of you will say to use ON DUPLICATE KEY and make column phone_number primary key but that won't work as contract type column could be TABLET which has no phone number.

IF EXISTS (SELECT network_contract_id FROM network_contracts WHERE phone_number = 123456 AND user_id = 1)

	UPDATE  network_contracts
	SET     verification_pin = 9891
	WHERE   user_id = 1

ELSE 

	INSERT  INTO network_contracts(user_id, phone_number, contract_type, verification_pin)
	VALUES  (1, 123456, 'Sim Only', 7676)

any help/advise would be greatly appreciated.

 

thank you guys

 

[requinix]

What does the "tablet" version of that look like?

[Destramic]

well the tablet would't have a verfication pin...but i planned on having 1 table ie. network_contracts to hoild users contratcs such as sim only, mobile and tablet.

 

the verification pin is for when a user adds sim only / mobile, a pin is then sent to users phone number which needs to be verified.

Edited February 19, 2017 by Destramic

[Barand]

Set a UNIQUE index on phone. Tablets can all have null values.

EG

CREATE TABLE `customers` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `fullName` varchar(50) DEFAULT NULL,
  `phone` varchar(45) DEFAULT NULL,
  `pin` int(11) DEFAULT NULL,
  `device` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_customers_phone` (`phone`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8;

mysql> select * from customers;
+----+----------+----------+------+--------+
| id | fullName | phone    | pin  | device |
+----+----------+----------+------+--------+
|  1 | Cust_1   | 12346789 | 1234 | phone  |
|  2 | Cust_2   | 12356789 | 3456 | phone  |
|  3 | Cust_3   | 12366789 | 5678 | phone  |
|  4 | Cust_4   | NULL     | NULL | tablet |
|  5 | Cust_5   | NULL     | NULL | tablet |
+----+----------+----------+------+--------+

INSERT INTO customers (fullname,phone,pin,device)
VALUES ('Cust_6', 12346789, 1010, 'phone')
ON DUPLICATE KEY UPDATE pin = 1010;

mysql> select * from customers;
+----+----------+----------+------+--------+
| id | fullName | phone    | pin  | device |
+----+----------+----------+------+--------+
|  1 | Cust_1   | 12346789 | 1010 | phone  |   <-- updated
|  2 | Cust_2   | 12356789 | 3456 | phone  |
|  3 | Cust_3   | 12366789 | 5678 | phone  |
|  4 | Cust_4   | NULL     | NULL | tablet |
|  5 | Cust_5   | NULL     | NULL | tablet |
+----+----------+----------+------+--------+

[Destramic]

i wouldnt of thought of that...thank you barand for your time and effort