Solved

MYSQL query error - Help required to debug it -

Posted on 2011-03-25
8
436 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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 

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
 
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 SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

825 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