Re: multiple tables - mysql 3.x and 4.x compatibility

[Nick Roper]

reza,

The following statements should work:

===========================

select p.product_model, t.price
into outfile 'prices_temp.txt'
from products as p, products_temp as t
where p.product_model = t.product_no;

load data infile 'prices_temp.txt'
replace into table products (product_model, product_price);

===========================

You could save this in a file - say 'update_prices.sql' and then execute it as follows:

mysql> source path/to/update_prices.sql

However, you will need to delete 'prices_temp.txt' after the statements are executed - otherwise you'll get a 'file already exists' error next time the 'select ... into outfile ...' executes. You will also need FILE privileges to execute the statements. You could get round this by generating a different filename each time - maybe using the date & time as part of the name.

There may be a neater way of doing this with MySQL statements, but the easiest approach will probably be to do the updates with the scripting language, e.g. PHP or Perl, that is used for the interface between the website and MySQL.

Nick Roper