Require query to transfer data from Sugarcrm to tailor made system

Posted on 2011-02-23
Last Modified: 2016-03-18

I need a query that will tranfer the data from one table into another one, I have work on this for a bit, but I am getting stuck and frustated (I am not a programer).

If you are familier with sugarcrm it contains

1. The account or cllient table
2. The Contact Table
3. and a table that stores the id values for contact and accounts that are link to each other.

I have created a new table for contacts on my tailor made system and I need to
copy the "accountid" into the table of "contacts" from the table "number 3". where "contactid" in table "number 3" is = "ContactID" on the "contact table" I have tryed diferent sql queries but I alway get the duplicate id error or the row returns more than one ?????. I can not pass this point the error I get is one or the other one.

Contact table =
contact name
Phone number
and I have created a new field called "companyID" where the new values need to be stored.

Link table =

I really had enough of spending hours looking for a solution on line, can someone please
write a query that I can put into PHPmyadmin to copy the values over?

For me this question is very difficult as I have been on it for some time now.
Question by:DPP2011
  • 6
  • 6
LVL 77

Expert Comment

ID: 34966911
insert into tablename (list of columns from tablename) values (select column1, column2,column3 from tablename2)

Author Comment

ID: 34967078
Hello and thank you for the answer.

Your query copy the existing records from one table to another one,

I need to transfer "AccountID" from table "Number3" into and existing table with data, called table "Contacts" Field"accountID"
from  Table "Number3" where "contactID" in table Cotacts = "contactID" fron table number3.
LVL 77

Expert Comment

ID: 34967305
Could you post the create table for each table. (show create table tablename)
And which columns you want from one into the other.

What do you want to do?

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.


Author Comment

ID: 34967928

FIELD: contact name
FIELD: address
FIELD: Phone number
FIELD: companyID New field, empty


What I want to do?

Insert Into contact (companyID) Select companyID From  LINK
where Link.contactid = Contact.contactid;

I get error "unknown column" error

I need to copy or insert all the companyid data from the Link table into the
contact table WHERE the ContactID in table Contact is equal to the ContactID in the link table.

LVL 77

Expert Comment

ID: 34970074
First, you do not have a link.conactid column.
You need an update and not an insert since you seem to only have one column updated.

The other part is you need to use join in the select You are running an update rather than an insert

update contact (CompanyId)

You might be better off considering using a FK and alterting the table to add the parameter.

Link is a relationship table and I'm not sure what or why you are trying to get the data into contact table.

Author Comment

ID: 34974601
Thank you.

My request was very simple, I DON’T KNOW HOW TO DO IT and I have spent quite some time trying to figure it out. That is why I requested for someone to DO the query for me as time goes by and I am still not able to do it.

I don't know what FK is - as explain in the first email I am not a programer.

The reason WHY I am doing it, is Irrelevant. The point here is that I need to do it and I am asking
If there is anyone that can do it for me.

Giving me explanations as to what is wrong With the query I did, and the fact that the request I am making does not make sence to you is just more frustrating, while what you have written in the previous reply make perfect sence to you and other programers, to me it just sound like a new riddle that I have to figuere out before I can get on with the data transfer.

This is adding more confusion to a person that is allready confused on how to get it done, That is why I have requested for someone to do the query for me, So I can SEE how it is done the write way therefore clearing my confusion and getting the transfer done.

So, If you know and can do it, Please post the query.
LVL 77

Expert Comment

ID: 34975099

The problem is that you have data in the contact table and you want to update one column in the same table.

Link is your relationship table that you can query with a join to match the contactID (ID in contact) with the CompanyID table.

update contact (ID, "contact name",address,"phone number",companyID) values (select," name",a.address," number",b.companyID from contact a join on LINK b where a.ID=b.contactID)

run the select query first to make sure the data is correct and is what you are looking for:
select," name",a.address," number",b.companyID from contact a join on LINK b where a.ID=b.contactID

You have spaces in column names in the example, so just doublcheck  

Please post the show create table tablename for contact and link

Author Comment

ID: 34975542

"The problem is that you have data in the contact table and you want to update one column in the same table." Yes, it is true.

The company I work for is using 3 separate databases to achieve 3 separate functions and a new system that can do all the functions require without having duplicate databases and this system require the Linking field to be on contact table in order to show the contacts details for a particular company. It does not work with a link table.

The reason why I have ask for the query is because I have tryied doing the update and inset in the past and I bounced between errors generated from my querry without being able to get anything done.

The following is the create table for both tables:


CREATE TABLE `sugar`.`contacts` (
`id` char( 36 ) NOT NULL ,
`account_id` 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 ,


CREATE TABLE `sugar`.`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` )
LVL 77

Expert Comment

ID: 34977161
You have to use perl/php that will go through row at a time and generate an update.
Unless you are familiar with setting up stored procedures.
i.e. select @variable=column from ...

There is no "simple" way to do it within a single mysql query.


Author Comment

ID: 34977283

Thank you.

No, I am not familiar with the stored procedures.

I guess it might be easier to use excell to copy and paste the data and then imported into mysql.

LVL 77

Accepted Solution

arnold earned 500 total points
ID: 34980901
You could use queries from existing data to create a view from which you can then use to insert into tablename (select columns from the view).

perl is an interpreted language that can be used to query/collect/aggregate data and then generate the inserts into the new table.

I'd suggest you engineer/design the new database to use foreign keys etc to tie the data together

Get the mysql workbench and use it to design the new database tables, relationships, etc. The design would also optimize the performance and possibly reduce duplication of information as well as avoid issues with entries deleted from one table, remain in anothe which compromises data integrity.


Author Closing Comment

ID: 34984534

You mentioned:
"I'd suggest you engineer/design the new database to use foreign keys etc to tie the data together"

Yes, I will have to learn how to do it, one of our systems uses the foreign key and the other one does not and I need to combine them both.

Thank you for your input on my problem.

Featured Post

MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

Question has a verified solution.

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

Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
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…
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

856 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