Require query to transfer data from Sugarcrm to tailor made system


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.
Who is Participating?
arnoldConnect With a Mentor Commented:
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.

insert into tablename (list of columns from tablename) values (select column1, column2,column3 from tablename2)
DPP2011Author Commented:
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.
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.

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?

DPP2011Author Commented:

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.

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.
DPP2011Author Commented:
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.

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
DPP2011Author Commented:

"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` )
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.

DPP2011Author Commented:

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.

DPP2011Author Commented:

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.
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.