• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 740
  • Last Modified:

Require query to transfer data from Sugarcrm to tailor made system

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
DPP2011
Asked:
DPP2011
  • 6
  • 6
1 Solution
 
arnoldCommented:
insert into tablename (list of columns from tablename) values (select column1, column2,column3 from tablename2)
0
 
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.
0
 
arnoldCommented:
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
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.

 
DPP2011Author Commented:

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

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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 6
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now