Solved

Require query to transfer data from Sugarcrm to tailor made system

Posted on 2011-02-23
12
705 Views
Last Modified: 2016-03-18
Hello,

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 =
ID
contact name
address
Phone number
and I have created a new field called "companyID" where the new values need to be stored.

Link table =
ID
COMPANYID,
CONTACTID.

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.
0
Comment
Question by:DPP2011
  • 6
  • 6
12 Comments
 
LVL 76

Expert Comment

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

Author Comment

by:DPP2011
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.
0
 
LVL 76

Expert Comment

by:arnold
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?

0
 

Author Comment

by:DPP2011
ID: 34967928

TABLES NAME "CONTACT"
FIELD : ID
FIELD: contact name
FIELD: address
FIELD: Phone number
FIELD: companyID New field, empty

TABLE NAME: "LINK"
FIELD: ID
FIELD: COMPANYID,
FIELD: CONTACTID.

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.

0
 
LVL 76

Expert Comment

by:arnold
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.
0
 

Author Comment

by:DPP2011
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.
0
What Is Threat Intelligence?

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

 
LVL 76

Expert Comment

by:arnold
ID: 34975099
http://www.ehow.com/way_5247249_mysql-foreign-key-tutorial.html

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
0
 

Author Comment

by:DPP2011
ID: 34975542
Hi,

"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:

CONTACT TABLE:

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 ,
`email2[...]

LINK TABLE:

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` )
) ENGINE = MYISAM DEFAULT CHARSET = utf8;
0
 
LVL 76

Expert Comment

by:arnold
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 ...

http://dev.mysql.com/doc/refman/5.0/en/user-variables.html

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


0
 

Author Comment

by:DPP2011
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.

0
 
LVL 76

Accepted Solution

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

0
 

Author Closing Comment

by:DPP2011
ID: 34984534
Hi,

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

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Suggested Solutions

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
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.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
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.

706 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

20 Experts available now in Live!

Get 1:1 Help Now