Solved

MYSQL query error - Help required to debug it -

Posted on 2011-03-25
8
434 Views
Last Modified: 2012-05-11
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 %&^&^%&^%&^&%&^%.
0
Comment
Question by:DPP2011
8 Comments
 
LVL 18

Expert Comment

by:Cluskitt
ID: 35214039
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
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 35214047

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
 
LVL 4

Expert Comment

by:qasim_md
ID: 35214114
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
 

Author Comment

by:DPP2011
ID: 35214142
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
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
LVL 4

Expert Comment

by:qasim_md
ID: 35214192
Try the SQL I have provided, it must work good for you...
0
 

Author Comment

by:DPP2011
ID: 35214193
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
 
LVL 4

Accepted Solution

by:
qasim_md earned 500 total points
ID: 35214196
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
 

Author Closing Comment

by:DPP2011
ID: 35214239
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

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
These days socially coordinated efforts have turned into a critical requirement for enterprises.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now