Update table if conditions met. (update the value from table b to a) corresponding value required

Question

I have a table a in which i have fields 'date', 'time', cost and order_id

table b which has fields 'year' 'month' 'hour' 'cost' and order_id fields.

both tables are linked with "order_id" field. i want to update table a if year, month, hour and order_id is same in both tables and update the corresponding value from table b to table a field "cost"

I have used this statement, but query is not working? what is wrong in it? i need help

UPDATE item a, cost b
    SET a.cost = b.cost
    WHERE a.order_id = b.order_id
    AND YEAR(a.date) = b.YEAR
    AND month(a.date) = b.month
    AND hour(a.time) = b.hour

score 2 · Accepted Answer · answered Aug 31 '15 at 06:40

2

UPDATE item a
JOIN cost b ON a.order_id = b.order_id
           AND YEAR(a.date) = b.YEAR
           AND month(a.date) = b.month
           AND hour(a.time) = b.hour
SET a.cost = b.cost

answered Aug 31 '15 at 06:40

juergen d

201,996
37
293
362

Thanks juergen for your quick support, i am have more than 20 cror records it taking time in updating. i will respond you as soon as i complete it. the best part is query is working Running..... – Kamran Mushtaq Aug 31 '15 at 06:57
It worked Hurrahh! Thanks alot juergen d – Kamran Mushtaq Aug 31 '15 at 07:31

Update table if conditions met. (update the value from table b to a) corresponding value required

1 Answers1

Linked