mysql - transfer data from one table to another??

macinslaw · 08-18-2004, 09:27 AM

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

ToniT · 08-18-2004, 10:33 AM

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

macinslaw · 08-18-2004, 12:00 PM

Thanks ToniT, but it seems that the syntax is different. By the way, what, if any, is the difference between mysql and postgre?

ToniT · 08-18-2004, 12:26 PM

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.

ToniT · 08-18-2004, 01:07 PM

After checking the MySql Documentation, it seems that the syntax used above should work.

macinslaw · 08-18-2004, 03:50 PM

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

macinslaw · 08-19-2004, 08:18 AM

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

ToniT · 08-19-2004, 08:41 AM

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.

macinslaw · 08-19-2004, 09:10 AM

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

ToniT · 08-19-2004, 10:03 AM

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

macinslaw · 08-19-2004, 12:20 PM

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

ToniT · 08-19-2004, 12:40 PM

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

macinslaw · 08-19-2004, 01:42 PM

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

macinslaw · 08-19-2004, 01:53 PM

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>

ToniT · 08-19-2004, 05:06 PM

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.