MYSQL query error - Help required to debug it -

I have the following 2 tables that I need to combine:

1.  TABLE `accounts_contacts` (
  `id` varchar(36) NOT NULL,
  `contact_id` varchar(36) DEFAULT NULL,
  `account_id` varchar(36) DEFAULT NULL,
  `date_modified` datetime DEFAULT NULL,
  `deleted` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `idx_acc_cont_acc` (`account_id`),
  KEY `idx_acc_cont_cont` (`contact_id`),
  KEY `idx_account_contact` (`account_id`,`contact_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

AND

TABLE `contacts` (
  `id` char(36) NOT NULL,
  `deleted` tinyint(1) NOT NULL DEFAULT '0',
  `date_entered` datetime NOT NULL,
  `date_modified` datetime NOT NULL,
  `modified_user_id` char(36) DEFAULT NULL,
  `assigned_user_id` char(36) DEFAULT NULL,
  `created_by` char(36) DEFAULT NULL,
  `salutation` varchar(5) DEFAULT NULL,
  `first_name` varchar(100) DEFAULT NULL,
  `last_name` varchar(100) DEFAULT NULL,
  `lead_source` varchar(100) DEFAULT NULL,
  `title` varchar(50) DEFAULT NULL,
  `department` varchar(100) DEFAULT NULL,
  `reports_to_id` char(36) DEFAULT NULL,
  `birthdate` date DEFAULT NULL,
  `do_not_call` varchar(3) DEFAULT '0',
  `phone_home` varchar(25) DEFAULT NULL,
  `phone_mobile` varchar(25) DEFAULT NULL,
  `phone_work` varchar(25) DEFAULT NULL,
  `phone_other` varchar(25) DEFAULT NULL,
  `phone_fax` varchar(25) DEFAULT NULL,
  `email1` varchar(100) DEFAULT NULL,
  `email2` varchar(100) DEFAULT NULL,
  `assistant` varchar(75) DEFAULT NULL,
  `assistant_phone` varchar(25) DEFAULT NULL,
  `email_opt_out` varchar(3) DEFAULT '0',
  `primary_address_street` varchar(150) DEFAULT NULL,
  `primary_address_city` varchar(100) DEFAULT NULL,
  `primary_address_state` varchar(100) DEFAULT NULL,
  `primary_address_postalcode` varchar(20) DEFAULT NULL,
  `primary_address_country` varchar(100) DEFAULT NULL,
  `alt_address_street` varchar(150) DEFAULT NULL,
  `alt_address_city` varchar(100) DEFAULT NULL,
  `alt_address_state` varchar(100) DEFAULT NULL,
  `alt_address_postalcode` varchar(20) DEFAULT NULL,
  `alt_address_country` varchar(100) DEFAULT NULL,
  `description` text,
  `portal_name` varchar(255) DEFAULT NULL,
  `portal_active` tinyint(1) NOT NULL DEFAULT '0',
  `portal_app` varchar(255) DEFAULT NULL,
  `invalid_email` tinyint(1) DEFAULT '0',
  `campaign_id` char(36) DEFAULT NULL,
  `account_id` char(36) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_cont_last_first` (`last_name`,`first_name`,`deleted`),
  KEY `idx_contacts_del_last` (`deleted`,`last_name`),
  KEY `idx_cont_del_reports` (`deleted`,`reports_to_id`,`last_name`),
  KEY `idx_cont_assigned` (`assigned_user_id`),
  KEY `idx_cont_email1` (`email1`),
  KEY `idx_cont_email2` (`email2`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

I need to create a new table (contacts2) with all the data from table 2 and  the `account_id` from table 1.

account_id needs to be added into the correct record where table1 contact_id= table2.id,
I have created the following query but I am getting syntax error and can't find my error.

 INSERT INTO contacts2 (`id`, `deleted`, `date_entered`, `date_modified`, `modified_user_id`, `assigned_user_id`, `created_by`, `salutation`, `first_name`, `last_name`, `lead_source`, `title`, `department`, `reports_to_id`, `birthdate`, `do_not_call`, `phone_home`, `phone_mobile`, `phone_work`, `phone_other`, `phone_fax`, `email1`, `email2`, `assistant`, `assistant_phone`, `email_opt_out`, `primary_address_street`, `primary_address_city`, `primary_address_state`, `primary_address_postalcode`, `primary_address_country`, `alt_address_street`, `alt_address_city`, `alt_address_state`, `alt_address_postalcode`, `alt_address_country`, `description`, `portal_name`, `portal_active`, `portal_app`, `invalid_email`, `campaign_id`, `account_id`) 
SELECT `contact`.`id` as `id`, `contact`.`deleted` as `deleted`, `contact`.`date_entered` as `date_entered`, `contact`.`date_modified` as `date_modified`, `contact`.`modified_user_id` as `modified_user_id`, `contact`.`assigned_user_id` as `assigned_user_id`, `contact`.`created_by` as `created_by`, `contact`.`salutation` as `salutation`, `contact`.`first_name` as `first_name`, `contact`.`last_name` as `last_name`, `contact`.`lead_source` as `lead_source`, `contact`.`title` as `title`, `contact`.`department` as `department`, `contact`.`reports_to_id` as `reports_to_id`, `contact`.`birthdate` as `birthdate`, `contact`.`do_not_call` as `do_not_call`, `contact`.`phone_home` as `phone_home`, `contact`.`phone_mobile` as `phone_mobile`, `contact`.`phone_work` as `phone_work`, `contact`.`phone_other` as `phone_other`, `contact`.`phone_fax` as `phone_fax`, `contact`.`email1` as `email1`, `contact`.`email2` as `email2`, `contact`.`assistant` as `assistant`, `contact`.`assistant_phone` as `assistant_phone`, `contact`.`email_opt_out` as `email_opt_out`, `contact`.`primary_address_street` as `primary_address_street`, `contact`.`primary_address_city` as `primary_address_city`, `contact`.`primary_address_state` as `primary_address_state`, `contact`.`primary_address_postalcode`as `primary_address_postalcode`, `contact`.`primary_address_country`as `primary_address_country`, `contact`.`alt_address_street` as `alt_address_street`, `contact`.`alt_address_city` as `alt_address_city`, `contact`.`alt_address_state` as `alt_address_state`, `contact`.`alt_address_postalcode` as `alt_address_postalcode`, `contact`.`alt_address_country` as `alt_address_country`, `contact`.`description` as `description`, `contact`.`portal_name` as `portal_name`, `contact`.`portal_active` as `portal_active`, `contact`.`portal_app` as `portal_app`, `contact`.`invalid_email` as `invalid_email`, `contact`.`campaign_id` as `campaign_id`, `accounts_contacts`.`account_id` 
WHERE `accounts_contacts`.`contact_id`=`contacts`.`id`
 

Open in new window



I need a fresh pair of eyes to help me debug this, at this point all I see is %&^&^%&^%&^&%&^%.
DPP2011Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
qasim_mdConnect With a Mentor Commented:
Try this it must work now:::

INSERT INTO contacts2 (`id`, `deleted`, `date_entered`, `date_modified`, `modified_user_id`, `assigned_user_id`, `created_by`, `salutation`, `first_name`, `last_name`, `lead_source`, `title`, `department`, `reports_to_id`, `birthdate`, `do_not_call`, `phone_home`, `phone_mobile`, `phone_work`, `phone_other`, `phone_fax`, `email1`, `email2`, `assistant`, `assistant_phone`, `email_opt_out`, `primary_address_street`, `primary_address_city`, `primary_address_state`, `primary_address_postalcode`, `primary_address_country`, `alt_address_street`, `alt_address_city`, `alt_address_state`, `alt_address_postalcode`, `alt_address_country`, `description`, `portal_name`, `portal_active`, `portal_app`, `invalid_email`, `campaign_id`, `account_id`)
SELECT `contact`.`id` as `id`, `contact`.`deleted` as `deleted`, `contact`.`date_entered` as `date_entered`, `contact`.`date_modified` as `date_modified`, `contact`.`modified_user_id` as `modified_user_id`, `contact`.`assigned_user_id` as `assigned_user_id`, `contact`.`created_by` as `created_by`, `contact`.`salutation` as `salutation`, `contact`.`first_name` as `first_name`, `contact`.`last_name` as `last_name`, `contact`.`lead_source` as `lead_source`, `contact`.`title` as `title`, `contact`.`department` as `department`, `contact`.`reports_to_id` as `reports_to_id`, `contact`.`birthdate` as `birthdate`, `contact`.`do_not_call` as `do_not_call`, `contact`.`phone_home` as `phone_home`, `contact`.`phone_mobile` as `phone_mobile`, `contact`.`phone_work` as `phone_work`, `contact`.`phone_other` as `phone_other`, `contact`.`phone_fax` as `phone_fax`, `contact`.`email1` as `email1`, `contact`.`email2` as `email2`, `contact`.`assistant` as `assistant`, `contact`.`assistant_phone` as `assistant_phone`, `contact`.`email_opt_out` as `email_opt_out`, `contact`.`primary_address_street` as `primary_address_street`, `contact`.`primary_address_city` as `primary_address_city`, `contact`.`primary_address_state` as `primary_address_state`, `contact`.`primary_address_postalcode`as `primary_address_postalcode`, `contact`.`primary_address_country`as `primary_address_country`, `contact`.`alt_address_street` as `alt_address_street`, `contact`.`alt_address_city` as `alt_address_city`, `contact`.`alt_address_state` as `alt_address_state`, `contact`.`alt_address_postalcode` as `alt_address_postalcode`, `contact`.`alt_address_country` as `alt_address_country`, `contact`.`description` as `description`, `contact`.`portal_name` as `portal_name`, `contact`.`portal_active` as `portal_active`, `contact`.`portal_app` as `portal_app`, `contact`.`invalid_email` as `invalid_email`, `contact`.`campaign_id` as `campaign_id`, `accounts_contacts`.`account_id`
from contacts contact INNER JOIN accounts_contacts accounts_contacts
WHERE `accounts_contacts`.`contact_id`=`contact`.`id`
0
 
CluskittCommented:
SELECT `id`, `deleted`, `date_entered`, `date_modified`, `modified_user_id`, `assigned_user_id`, `created_by`, `salutation`, `first_name`, `last_name`, `lead_source`, `title`, `department`, `reports_to_id`, `birthdate`, `do_not_call`, `phone_home`, `phone_mobile`, `phone_work`, `phone_other`, `phone_fax`, `email1`, `email2`, `assistant`, `assistant_phone`, `email_opt_out`, `primary_address_street`, `primary_address_city`, `primary_address_state`, `primary_address_postalcode`, `primary_address_country`, `alt_address_street`, `alt_address_city`, `alt_address_state`, `alt_address_postalcode`, `alt_address_country`, `description`, `portal_name`, `portal_active`, `portal_app`, `invalid_email`, `campaign_id`, `account_id`
INTO contacts2
FROM contacts INNER JOIN accounts_contacts ON accounts_contacts.id=contacts .id
0
 
Pratima PharandeCommented:

I think you missed
From `contacts`, `accounts_contacts`

From the select query

Try this

  INSERT INTO contacts2 (`id`, `deleted`, `date_entered`, `date_modified`, `modified_user_id`, `assigned_user_id`, `created_by`, `salutation`, `first_name`, `last_name`, `lead_source`, `title`, `department`, `reports_to_id`, `birthdate`, `do_not_call`, `phone_home`, `phone_mobile`, `phone_work`, `phone_other`, `phone_fax`, `email1`, `email2`, `assistant`, `assistant_phone`, `email_opt_out`, `primary_address_street`, `primary_address_city`, `primary_address_state`, `primary_address_postalcode`, `primary_address_country`, `alt_address_street`, `alt_address_city`, `alt_address_state`, `alt_address_postalcode`, `alt_address_country`, `description`, `portal_name`, `portal_active`, `portal_app`, `invalid_email`, `campaign_id`, `account_id`)
SELECT `contact`.`id` as `id`, `contact`.`deleted` as `deleted`, `contact`.`date_entered` as `date_entered`, `contact`.`date_modified` as `date_modified`, `contact`.`modified_user_id` as `modified_user_id`, `contact`.`assigned_user_id` as `assigned_user_id`, `contact`.`created_by` as `created_by`, `contact`.`salutation` as `salutation`, `contact`.`first_name` as `first_name`, `contact`.`last_name` as `last_name`, `contact`.`lead_source` as `lead_source`, `contact`.`title` as `title`, `contact`.`department` as `department`, `contact`.`reports_to_id` as `reports_to_id`, `contact`.`birthdate` as `birthdate`, `contact`.`do_not_call` as `do_not_call`, `contact`.`phone_home` as `phone_home`, `contact`.`phone_mobile` as `phone_mobile`, `contact`.`phone_work` as `phone_work`, `contact`.`phone_other` as `phone_other`, `contact`.`phone_fax` as `phone_fax`, `contact`.`email1` as `email1`, `contact`.`email2` as `email2`, `contact`.`assistant` as `assistant`, `contact`.`assistant_phone` as `assistant_phone`, `contact`.`email_opt_out` as `email_opt_out`, `contact`.`primary_address_street` as `primary_address_street`, `contact`.`primary_address_city` as `primary_address_city`, `contact`.`primary_address_state` as `primary_address_state`, `contact`.`primary_address_postalcode`as `primary_address_postalcode`, `contact`.`primary_address_country`as `primary_address_country`, `contact`.`alt_address_street` as `alt_address_street`, `contact`.`alt_address_city` as `alt_address_city`, `contact`.`alt_address_state` as `alt_address_state`, `contact`.`alt_address_postalcode` as `alt_address_postalcode`, `contact`.`alt_address_country` as `alt_address_country`, `contact`.`description` as `description`, `contact`.`portal_name` as `portal_name`, `contact`.`portal_active` as `portal_active`, `contact`.`portal_app` as `portal_app`, `contact`.`invalid_email` as `invalid_email`, `contact`.`campaign_id` as `campaign_id`, `accounts_contacts`.`account_id`
From `contacts`, `accounts_contacts`
WHERE `accounts_contacts`.`contact_id`=`contacts`.`id`
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
qasim_mdCommented:
Try this, let me  know if it helped ?

INSERT INTO contacts2 (`id`, `deleted`, `date_entered`, `date_modified`, `modified_user_id`, `assigned_user_id`, `created_by`, `salutation`, `first_name`, `last_name`, `lead_source`, `title`, `department`, `reports_to_id`, `birthdate`, `do_not_call`, `phone_home`, `phone_mobile`, `phone_work`, `phone_other`, `phone_fax`, `email1`, `email2`, `assistant`, `assistant_phone`, `email_opt_out`, `primary_address_street`, `primary_address_city`, `primary_address_state`, `primary_address_postalcode`, `primary_address_country`, `alt_address_street`, `alt_address_city`, `alt_address_state`, `alt_address_postalcode`, `alt_address_country`, `description`, `portal_name`, `portal_active`, `portal_app`, `invalid_email`, `campaign_id`, `account_id`)
SELECT `contact`.`id` as `id`, `contact`.`deleted` as `deleted`, `contact`.`date_entered` as `date_entered`, `contact`.`date_modified` as `date_modified`, `contact`.`modified_user_id` as `modified_user_id`, `contact`.`assigned_user_id` as `assigned_user_id`, `contact`.`created_by` as `created_by`, `contact`.`salutation` as `salutation`, `contact`.`first_name` as `first_name`, `contact`.`last_name` as `last_name`, `contact`.`lead_source` as `lead_source`, `contact`.`title` as `title`, `contact`.`department` as `department`, `contact`.`reports_to_id` as `reports_to_id`, `contact`.`birthdate` as `birthdate`, `contact`.`do_not_call` as `do_not_call`, `contact`.`phone_home` as `phone_home`, `contact`.`phone_mobile` as `phone_mobile`, `contact`.`phone_work` as `phone_work`, `contact`.`phone_other` as `phone_other`, `contact`.`phone_fax` as `phone_fax`, `contact`.`email1` as `email1`, `contact`.`email2` as `email2`, `contact`.`assistant` as `assistant`, `contact`.`assistant_phone` as `assistant_phone`, `contact`.`email_opt_out` as `email_opt_out`, `contact`.`primary_address_street` as `primary_address_street`, `contact`.`primary_address_city` as `primary_address_city`, `contact`.`primary_address_state` as `primary_address_state`, `contact`.`primary_address_postalcode`as `primary_address_postalcode`, `contact`.`primary_address_country`as `primary_address_country`, `contact`.`alt_address_street` as `alt_address_street`, `contact`.`alt_address_city` as `alt_address_city`, `contact`.`alt_address_state` as `alt_address_state`, `contact`.`alt_address_postalcode` as `alt_address_postalcode`, `contact`.`alt_address_country` as `alt_address_country`, `contact`.`description` as `description`, `contact`.`portal_name` as `portal_name`, `contact`.`portal_active` as `portal_active`, `contact`.`portal_app` as `portal_app`, `contact`.`invalid_email` as `invalid_email`, `contact`.`campaign_id` as `campaign_id`, `accounts_contacts`.`account_id`
from contacts contact INNER JOIN accounts_contacts accounts_contacts
WHERE `accounts_contacts`.`contact_id`=`contacts`.`id`
0
 
DPP2011Author Commented:
Hello and thank you for your fast response,

Cluskitt: I follow you reply and I get,
#1327 - Undeclared variable: contacts2

I have read on a forum:
Apparantly doing the SELECT INTO was not supported in MYSQL, to do what I want to do and that it needs to be done as:
INSERT INTO [tablename]
SELECT [fields],
but this data just comes from one forum so I don't know if it is true or not.

Pratima mcs: I have made the changes you suggested and I get,
#1054 - Unknown column 'contact.id' in 'field list'

0
 
qasim_mdCommented:
Try the SQL I have provided, it must work good for you...
0
 
DPP2011Author Commented:
Hello qasim_md:

Thank you, I get an error on the query:
#1054 - Unknown column 'contacts.id' in 'where clause'

All the data is helpful as I can see how other programers  structure the query.
0
 
DPP2011Author Commented:
Great,

Thank you, it works great.

Some times it makes me wonder why the MYSQL data management softwares do not come with this type of capabilities already in as part of the pakage.

Thank you again.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.