Jump to content

if row exists update else insert


Destramic
Go to solution Solved by Barand,

Recommended Posts

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

 

Link to comment
Share on other sites

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 by Destramic
Link to comment
Share on other sites

  • Solution

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 |
+----+----------+----------+------+--------+
  • Like 1
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.