[CLUE-Tech] MySQL 3.23

[Greg Knaddison]

How about - upgrade to the modern stable MySQL and use subqueries -
update products
set products.price = (select new_prices.price
                                from new_prices
                                where products.id = new_prices.id)
where products.id in (select new_prices.id
                                from new_prices)

OR

You can join the new prices to the old products table to create a temp 
table that has the right information and then drop your old products 
table and put the temp table in it's place maybe?

There's got to be a better solution that these two, I just don't know it.

Greg

David Willson wrote:

> I need to UPDATE a field in one table with the corresponding 
> information in another table.
> I have two tables:
> Products (
>   Id integer,
>   Price decimal(6,2)
>   )
> and
> New_Prices (
>   Id integer,
>   Price decimal(6,2)
>   )
>  
> I need to use the New_Prices.Price to overwrite the Products.Price.  
> How do I do it?  Anyone?  Anyone?  As an additional motivator, this is 
> ~easy~ in PostgreSQL.  Actually, I'm certain this is easy, and I'm 
> just missing it...