proper sql syntax for updating values from one table to another

rastem

Hey all,

I read through the MySQL site and apparently what I want to do is easy...if you have a version of MySQL that I do not. I want to update values in one table into another. The easy way (and the way I cannot do it because I don't have MySQL after 4.04) is this:

UPDATE table1, table2 SET table1.value = table2.value WHERE table1.id = table2.id

I'm using phpMyAdmin to administer the DB, and it gives me an error when I run this. I figure it's because the SQL I have is 3.x. So how does one do this in 3.x SQL?

Ras.

vladibo

As far as I know you can not do this in mysql with one operation

Drakla

I'm just writing a stock control thing as a test, where basically you have an orders table - if items in the orders table are in there a long time you just put them back on the shelf, so to speak.

Ideally I wanted this kind of pseudo query:

UPDATE stocktable SET stocklev=stocklev+orders.stock WHERE stocktable.itemid=ordertable.itemid AND ordertable.date=TOOOLD;

Without having to code up any extra php my solution was this:

create temporary table tempy AS SELECT stocktab.stockid,stocktab.stocklev+orderlimbo.stocklev
FROM stocktab, orderlimbo
WHERE stocktab.stockid=orderlimbo.stockid;

REPLACE stocktab SELECT * FROM tempy;

And that's it. Admittedly my tables were trivial

UPDATE table1, table2 SET table1.value = table2.value WHERE table1.id = table2.id

Actually for yours it should be as simple as:

REPLACE table1 SELECT * from table2;

If the table schemas are the same, otherwise you can put in the files

REPLACE table1 (field1, field2) SELECT val1,val2 from table2;

Hope I haven't completely cocked that up now, give me a poke if I have.