Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


Require query to transfer data from Sugarcrm to tailor made system

Posted on 2011-02-23
Medium Priority
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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 6
LVL 80

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 80

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?

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.


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 80

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 80

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 a.id,"a.contact name",a.address,"a.phone 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 a.id,"a.contact name",a.address,"a.phone 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 80

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 80

Accepted Solution

arnold earned 1500 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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
In this article, we’ll look at how to deploy ProxySQL.
The viewer will learn how to count occurrences of each item in an array.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

636 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