LinuxQuestions.org
Share your knowledge at the LQ Wiki.
Go Back   LinuxQuestions.org > Forums > Non-*NIX Forums > Programming
User Name
Password
Programming This forum is for all programming questions.
The question does not have to be directly related to Linux and any language is fair game.

Notices

Reply
 
Search this Thread
Old 08-18-2004, 09:27 AM   #1
macinslaw
LQ Newbie
 
Registered: Aug 2004
Posts: 14

Rep: Reputation: 0
mysql - transfer data from one table to another??


Okay, I'm having a great deal of trouble and so far no forum has been able to answer. I hope this one will be different! LOL

System:
AMD 2800+
Ram 512 Mb
HD 120 Gb
OS Mandrake 10
Latest Mysql
Latest PHP
Latest Apache

Problem:
I have data in 2 different tables, needed to import data from access database into MySQL. Tried navicatas well as several other programs, but primary data in access file corrupt. Finally ported all data from access file into mysql table, but I need to transfer a field from aone table into another field of another table.

I was trying the following command to no avail:

update [table1.field] set table1.field='table2.field' where table2.field='value';

but it doesn't work. Anyone help?

-Macinslaw
 
Old 08-18-2004, 10:33 AM   #2
ToniT
Senior Member
 
Registered: Oct 2003
Location: Zurich, Switzerland
Distribution: Debian/unstable
Posts: 1,357

Rep: Reputation: 47
There is no query from table2.

Don't know about mysql, but in postgres thing can be done as:
Code:
bar=> BEGIN;
BEGIN

bar=> \d lahna;
            Table "public.lahna"
 Column |         Type          | Modifiers 
--------+-----------------------+-----------
 sar    | integer               | 
 tarina | character varying(50) | 

bar=> select * from lahna;
 sar |  tarina   
-----+-----------
   9 | Niinpäs..
   4 | Sienevöö
  13 | Koira
   3 | Uugl..
  11 | Toinen
(5 rows)

bar=> CREATE TABLE lahna2 (otherField varchar(20),destinationField varchar(20),moreMess varchar(20));
CREATE TABLE
bar=> insert into lahna2 select NULL,tarina,'Some input' from lahna;
INSERT 0 5
bar=> select * from lahna;
 sar |  tarina   
-----+-----------
   9 | Niinpäs..
   4 | Sienevöö
  13 | Koira
   3 | Uugl..
  11 | Toinen
(5 rows)

bar=> select * from lahna2;
 otherfield | destinationfield |  moremess  
------------+------------------+------------
            | Niinpäs..        | Some input
            | Sienevöö         | Some input
            | Koira            | Some input
            | Uugl..           | Some input
            | Toinen           | Some input
(5 rows)

bar=> -- Or alternative syntax: 
bar=> DROP TABLE lahna2;
DROP TABLE
bar=> CREATE TABLE lahna2 (otherField varchar(20),destinationField varchar(20),moreMess varchar(20));
CREATE TABLE
bar=> insert into lahna2 (destinationField,moreMess) select tarina,'Some input' from lahna;
INSERT 0 5
bar=> select * from lahna2;
 otherfield | destinationfield |  moremess  
------------+------------------+------------
            | Niinpäs..        | Some input
            | Sienevöö         | Some input
            | Koira            | Some input
            | Uugl..           | Some input
            | Toinen           | Some input
(5 rows)

bar=>
 
Old 08-18-2004, 12:00 PM   #3
macinslaw
LQ Newbie
 
Registered: Aug 2004
Posts: 14

Original Poster
Rep: Reputation: 0
Thanks ToniT, but it seems that the syntax is different. By the way, what, if any, is the difference between mysql and postgre?
 
Old 08-18-2004, 12:26 PM   #4
ToniT
Senior Member
 
Registered: Oct 2003
Location: Zurich, Switzerland
Distribution: Debian/unstable
Posts: 1,357

Rep: Reputation: 47
MySql is more lightweight database than Postgres.

Upsides are that it is somewhat easier to configure, IIRC has smaller footprint and is quite common in many web sollutions (that is, many scripts/tools are written directly to MySql).

Downsides are that it doesn't support all the syntax of SQL standard (eg. subselects). This means that migrating from other database system to MySql is not so easy and while it is being faster in small to medium-sized systems, it doesn't scale so well to large systems as postgres.

MySql is catching up in all these areas, though.


About the original problem: you probably have to write the previous sentence using unions so that MySql can handle it.
 
Old 08-18-2004, 01:07 PM   #5
ToniT
Senior Member
 
Registered: Oct 2003
Location: Zurich, Switzerland
Distribution: Debian/unstable
Posts: 1,357

Rep: Reputation: 47
After checking the MySql Documentation, it seems that the syntax used above should work.
 
Old 08-18-2004, 03:50 PM   #6
macinslaw
LQ Newbie
 
Registered: Aug 2004
Posts: 14

Original Poster
Rep: Reputation: 0
Interesting...I shall endeavor to make good use of them then and make a more valiant attempt at transposing your instructions.

I am a novice in way over his head developing a web tracking application for a company. I thank you for your patience.

-Mac
 
Old 08-19-2004, 08:18 AM   #7
macinslaw
LQ Newbie
 
Registered: Aug 2004
Posts: 14

Original Poster
Rep: Reputation: 0
<<CLICK>>

Okay, ToniT, it finally hit me as to what you were doing....duh....I told you I was a novice!

I believe you are doing the following:

insert into table2 select fieldname, fieldname, data from table1;

correct?

thanks.
 
Old 08-19-2004, 08:41 AM   #8
ToniT
Senior Member
 
Registered: Oct 2003
Location: Zurich, Switzerland
Distribution: Debian/unstable
Posts: 1,357

Rep: Reputation: 47
yes, or alternative syntax:
insert into table2 (fieldOfTable2) select fieldOfTable1 from table1;
I think number of fields in parenthesis has to be the same as in select expression.
 
Old 08-19-2004, 09:10 AM   #9
macinslaw
LQ Newbie
 
Registered: Aug 2004
Posts: 14

Original Poster
Rep: Reputation: 0
Gotcha, but I need to run a comparison....Let me go into further details:

table Products;

prodid
model
brand

table job;

jobid
customerid
manufacturer
serial
model


current value of job.model = 'prodid' from table Products

I need to do the following:

select the contents from job.model compare that value to the prodid from the Products table, when it finds the correct value, then take the model number from the Products table and enter that data back into job.model

So it seems to me that there are 2 processes going on.

1. Find the prodid that matches the model in the job table
2. match that to the model in the products table
3. insert the model number from the Products table into the model field in the job table.

No matter how I think about it, these 3 items seems to have to be performed...does your method still work for this? If so, I'm not seeing how....I understand your syntax, but it seems to be a basic import, or dump, from one table to another and I need something a little smarter than that.

Am I making any sense?

-Mac
 
Old 08-19-2004, 10:03 AM   #10
ToniT
Senior Member
 
Registered: Oct 2003
Location: Zurich, Switzerland
Distribution: Debian/unstable
Posts: 1,357

Rep: Reputation: 47
A handholding example:

The script:
Code:
-- Make all operations temporary
BEGIN;

-- The tables

CREATE TABLE Products (
	prodid integer primary key ,
	model text, 
	brand text
);

CREATE TABLE Job (
	jobid integer NOT NULL,
	customerid integer,
	manufacturer integer,
	serial text,
	model text not null -- references Products(prodid)
);


-- Sample data:
COPY Products (prodid,model,brand) FROM stdin;
1	get this one	forget this one
2	get this two	forget this two
3	get this three	forget this three
\.


COPY Job (jobid, serial, model) FROM stdin;
21	irrelevant one	1
22	irrelevant two	2
23	irrelevant three	3
\.


-- Show the data:

select * from Products;
select * from Job;

-- The query: 
-- select the contents from job.model compare that value to
-- the prodid from the Products table, when it finds the correct
-- value, then take the model number from the Products table and enter
-- that data back into job.model

UPDATE job set model = p.model from Products as p where job.model=p.prodid;

-- Show new data:
select * from Products;
select * from Job;

-- Rollback:
ROLLBACK;
The result:
Code:
BEGIN
CREATE TABLE
CREATE TABLE
 prodid |     model      |       brand       
--------+----------------+-------------------
      1 | get this one   | forget this one
      2 | get this two   | forget this two
      3 | get this three | forget this three
(3 rows)

 jobid | customerid | manufacturer |      serial      | model 
-------+------------+--------------+------------------+-------
    21 |            |              | irrelevant one   | 1
    22 |            |              | irrelevant two   | 2
    23 |            |              | irrelevant three | 3
(3 rows)

UPDATE 3
 prodid |     model      |       brand       
--------+----------------+-------------------
      1 | get this one   | forget this one
      2 | get this two   | forget this two
      3 | get this three | forget this three
(3 rows)

 jobid | customerid | manufacturer |      serial      |     model      
-------+------------+--------------+------------------+----------------
    21 |            |              | irrelevant one   | get this one
    22 |            |              | irrelevant two   | get this two
    23 |            |              | irrelevant three | get this three
(3 rows)

ROLLBACK
 
Old 08-19-2004, 12:20 PM   #11
macinslaw
LQ Newbie
 
Registered: Aug 2004
Posts: 14

Original Poster
Rep: Reputation: 0
Sorry to be a pain....

UPDATE job set model = p.model from Products as p where job.model=p.prodid;

I follow everything except you introduced something new in this query. The 'as', I haven't seen this before in a query, can you please explain?

Also, I assume that you shorthanded p.model instead of Products.model...correct, or incorrect?

Thanks again.

Scott
 
Old 08-19-2004, 12:40 PM   #12
ToniT
Senior Member
 
Registered: Oct 2003
Location: Zurich, Switzerland
Distribution: Debian/unstable
Posts: 1,357

Rep: Reputation: 47
Well, Yes and no. Mostly yes.

In this case, you can drop that "as p" away and replace "p." with "Products.", but in general that as -operator is sometimes necessary to distinquish the fields, especially in cases when you do unions of two queries from same table.

The psychological reason why I put that "as" in the first place, is that there is two model fields, one in jobs and other in Products and I don't want to mix those and using general tablenames doesn't always work (as described in previous paragraph).
 
Old 08-19-2004, 01:42 PM   #13
macinslaw
LQ Newbie
 
Registered: Aug 2004
Posts: 14

Original Poster
Rep: Reputation: 0
Okay, thanks for helping an old man! I had found already that the general table names didn't work with my rather rudimentary queries and would need the more specific. Unfortunately, after conversing with my friends, in the database game, they were unable to come up with a query that functioned. I, by trade, am a Network Enginerr/Consultant. As there are few jobs in this capacity these days, I am trying to learn something new to become employable again.

(I realize that you didn't ask, but thought I should defend myself a little from sounding too much like a complete moron! LOL)

Anyway, I fully understand your information now. Thanks, I will report my results!

-Mac
 
Old 08-19-2004, 01:53 PM   #14
macinslaw
LQ Newbie
 
Registered: Aug 2004
Posts: 14

Original Poster
Rep: Reputation: 0
Okay...tried the command. Sorry to be a real pain...

this is the message:

ERROR 1064: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'from Products as Products where job.model = Products.ProductID' at
mysql>

Last edited by macinslaw; 08-19-2004 at 01:54 PM.
 
Old 08-19-2004, 05:06 PM   #15
ToniT
Senior Member
 
Registered: Oct 2003
Location: Zurich, Switzerland
Distribution: Debian/unstable
Posts: 1,357

Rep: Reputation: 47
ok, I installed and configured MySql server for myself and tested it.

It didn't work. MySql doesn't support transactions (BEGIN&ROLLBACK bug badly; I had to manually delete the tables ocassionally), the client has problems reading multiline entries from terminal, the documentation of mysql is not DFSG-free, the indentifiers are case sensitive and the UPDATE statement doesn't support "from" statement. Suddenly I start to remember all the reasons why I personally prefer Postgres over MySql. Somebody has once said that MySql is a toy database, I'm geting touch to the ones mind patterns.

Don't get me wrong. Postgresql has its problems too. eg. the "serial" (roughly equal to MySql's auto-increment) datatype has problems keeping on track because of design issues (it can be fixed to work as most people expect, but needs some triggers and serverside programming with pl/pgsql).


Enough of this rant (it felt good) and back to the business. The original problem (MySql not supporting from clause in update statement) can be circumvented by using syntax:
Code:
UPDATE Job,Products set Job.model = Products.model where Job.model=Products.prodid;
This happens to do the same thing as previous one, but has a difference in mental level.

What we did before (UPDATE job set model = p.model from Products as p where job.model=p.prodid, we said that:
-We want to update the job table.
-We want to set some of its values (especially model).
-To figure out new values, we have to gather data from other table (Products)
-But we don't apply this command to every possible combinations of all the data, but only where j.model and p.prodid match.

What we do now is:
-We want to update tables job and products.
-We want to set job.model to some value (products.model)
-But not all job.models to all products.models, just combinations where j.model matches with p.prodid.
As can be seen, products table wasn't really updated, but we had to claim that to get data out from it.

Last edited by ToniT; 08-19-2004 at 05:09 PM.
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Problem inserting data into a mysql table using PHP Rockgod2099 Programming 13 08-03-2005 12:27 AM
why do i get this error when inserting data to a mysql table verbatim Programming 2 06-15-2005 06:12 AM
MySQL non-realtime table-by-table mirroring Passive Linux - Software 1 01-20-2004 12:11 PM
How to import MS ACCESS Table including OLE filed into the MySQL Table ? myunicom Linux - General 1 11-28-2003 11:30 AM
retriving mysql data, and placing it inside a table in html using perl rhuser Programming 1 03-12-2003 05:04 AM


All times are GMT -5. The time now is 09:04 PM.

Main Menu
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
identi.ca: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration