text: Q:
  
  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.
  
  A:
  
  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
  
  