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 |
There is no query from table2.
Don't know about mysql, but in postgres thing can be done as: Code:
bar=> BEGIN; |
Thanks ToniT, but it seems that the syntax is different. By the way, what, if any, is the difference between mysql and postgre?
|
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. |
After checking the MySql Documentation, it seems that the syntax used above should work.
|
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 |
<<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. |
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. Am I making any sense? -Mac |
A handholding example:
The script: Code:
-- Make all operations temporary Code:
BEGIN |
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 |
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). |
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 |
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; 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. |
All times are GMT -5. The time now is 09:49 PM. |