tricky update sql statement with multiple tables joined

[Bastien]

I have this query which does a select on 2 tables listed

$sql  = "select o.orderid, sum(p.weight * o.amount) as weight
         from
             xcart_order_details o
         inner join
             xcart_products p
         on
             o.productid = p.productid
         group by o.orderid
         order by o.orderid";

The problem is that i really need to update a third table with the results of this first query. I am a little confused as to how to manage the 2 joins needed to do the update. I have

update xcart_orders o
          inner join xcart_order_details d
          inner join xcart_products p
          on
            o.orderid = d.orderid
          on
            d.productid = p.productid
          set total_weight = sum(p.weight * o.amount)
          where status = 'C' and order_shipped = 0

Where have I gone wrong?

Bastien

I wish my computer would do what I want it to do,
instead of what I tell it to do...

 

[kittyyo]

Maybe this way?

update xcart_orders o SET o.total_weight =
(
   SELECT sum(p.weight * d.amount)
     FROM xcart_order_details d
          inner join xcart_products p
          on d.productid = p.productid
     WHERE d.orderid = o.orderid
     GROUP BY d.orderid
)
where o.status = 'C' and o.order_shipped = 0

- is not tested, just an idea.

 

[Bastien]

can't, mysql version does not support subselects, or this one would have been real easy

Bastien

I wish my computer would do what I want it to do,
instead of what I tell it to do...

 

[TonyGroves]

Looks like a temporary table would be needed.

 

[Bastien]

yeah, thought about that today, that it will have to be a two step process...thanks for confirming

Bastien

I wish my computer would do what I want it to do,
instead of what I tell it to do...