CODEWITHSUNDEEP
mysqlsql
Jaylen
Jaylen

Reputation: 40279

How to check if a record exists then return 1 else return 0 in MySQL

I have a phone numbers database. in this database I have a column called "main_number" and it is a tinyint(1) type which I user 0 for not main number or 1 for main number.

The issue that I am having is that I do a bulk insert from one database to another using a stored procedure.

The business rules states that only one phone number can be marked as a main phone number. Now, what I need to do is to check if there is already a record that is marked "main_number" if so then the "main_number" should have the value 0. But if there is no record found then I should make main_number = 1.

The following is the defention of my table

CREATE TABLE `contact_numbers` (
 `number_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `account_id` int(10) unsigned DEFAULT NULL,
 `person_id` int(11) DEFAULT NULL,
 `contact_number` char(15) NOT NULL,
 `contact_extension` char(10) DEFAULT NULL,
 `contact_type` enum('Office','Fax','Reception','Direct','Cell','Toll Free','Home') NOT NULL DEFAULT 'Office',
 `contact_link` enum('Account','PDM','Other') NOT NULL DEFAULT 'Account',
 `status` tinyint(1) NOT NULL DEFAULT '1' COMMENT '0 = inactive, 1=active',
 `main_number` tinyint(1) NOT NULL DEFAULT '0' COMMENT '1 = main phone number',
 `created_on` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
 `created_by` int(11) NOT NULL,
 `modified_on` datetime DEFAULT NULL,
 `modified_by` int(11) NOT NULL DEFAULT '0',
 `external_id1` char(18) DEFAULT NULL COMMENT 'client''s account id',
 `external_id2` char(18) DEFAULT NULL COMMENT 'client''s person id',
 PRIMARY KEY (`number_id`),
 UNIQUE KEY `external_id1` (`external_id1`),
 UNIQUE KEY `external_id2` (`external_id2`),
 UNIQUE KEY `person_id_2` (`person_id`,`contact_type`),
 UNIQUE KEY `person_id_3` (`person_id`,`contact_number`,`contact_extension`),
 UNIQUE KEY `account_id_4` (`account_id`,`contact_number`,`contact_extension`),
 KEY `account_id` (`account_id`),
 KEY `person_id` (`person_id`),
 KEY `account_id_2` (`account_id`,`contact_link`,`main_number`),
 CONSTRAINT `cn_account_id` FOREIGN KEY (`account_id`) REFERENCES `accounts` (`account_id`) ON DELETE CASCADE ON UPDATE CASCADE,
 CONSTRAINT `cn_person_id` FOREIGN KEY (`person_id`) REFERENCES `contact_personal` (`person_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=114851 DEFAULT CHARSET=utf8

this is my query where I need to add a check to see if a record exists where account_id has main_number = 1.

SELECT ac.account_id, a.phone, 'Office', 'Account', 1 AS created_by
FROM rdi_ge_dev.account AS a
INNER JOIN finaltesting.accounts AS ac ON ac.external_id1 = a.SFDC_account_id

Note: I don't want to add a new unique index and I don't want to user on duplicate key update.

Upvotes: 1

Views: 3711

Answers (2)

Szekelygobe
Szekelygobe

Reputation: 2667

Or you can use an IF

SELECT ....
    IF(cn.number_id IS NULL, 1, 0) as main_number
FROM .....

Upvotes: 0

Michael Berkowski
Michael Berkowski

Reputation: 270599

By performing a LEFT JOIN against the contact_numbers table and looking for a NULL after limiting its results to main_number = 1 you can use a CASE in the SELECT list to assign 1 or 0 accordingly.

SELECT
  ac.account_id,
  a.phone,
  'Office',
  'Account',
  1 AS created_by,
  /* A null in the LEFT JOIN means it doesn't already exist */
  CASE WHEN cn.number_id IS NULL THEN 1 ELSE 0 END AS main_number
FROM
  rdi_ge_dev.account AS a
  INNER JOIN finaltesting.accounts AS ac ON ac.external_id1 = a.SFDC_account_id
  LEFT JOIN contact_numbers AS cn ON a.account_id = cn.account_id
WHERE 
  /* Limit the left join to only main_number = 1 */
  cn.main_number = 1

Upvotes: 2

Related Questions