ProgrammingThis forum is for all programming questions.
The question does not have to be directly related to Linux and any language is fair game.
Notices
Welcome to LinuxQuestions.org, a friendly and active Linux Community.
You are currently viewing LQ as a guest. By joining our community you will have the ability to post topics, receive our newsletter, use the advanced search, subscribe to threads and access many other special features. Registration is quick, simple and absolutely free. Join our community today!
Note that registered members see fewer ads, and ContentLink is completely disabled once you log in.
If you have any problems with the registration process or your account login, please contact us. If you need to reset your password, click here.
Having a problem logging in? Please visit this page to clear all LQ-related cookies.
Get a virtual cloud desktop with the Linux distro that you want in less than five minutes with Shells! With over 10 pre-installed distros to choose from, the worry-free installation life is here! Whether you are a digital nomad or just looking for flexibility, Shells can put your Linux machine on the device that you want to use.
Exclusive for LQ members, get up to 45% off per month. Click here for more info.
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';
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.
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.
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.
-- 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
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).
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!
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>
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.
LinuxQuestions.org is looking for people interested in writing
Editorials, Articles, Reviews, and more. If you'd like to contribute
content, let us know.