Search code examples
mysqlsqlnested

mysql nested select in update

Good morning,

I have a problem updating my Database.

This is my orders table:

id | parent_id | type | paid

Now I need to update all orders where the parent_id is paid. I have a little trouble doing this, because of nested queries.

I tried something like this

UPDATE orders
SET orders.paid = NOW()
WHERE (
    SELECT orders.parent_id
    FROM orders
    WHERE orders.id = orders.parent_id
)

But this won't do the magic. Can't get into this :/

1 | NULL | 8 | 2015-20-09 12:00:00
2 | 1    | 7 | 0000-00-00 00:00:00
3 | 1    | 7 | 0000-00-00 00:00:00
4 | NULL | 8 | 2015-18-09 12:00:00
5 | 4    | 7 | 0000-00-00 00:00:00
Solution

  • You may use JOIN for doing the update something as

    update orders o1
join orders o2 on o2.id = o1.parent_id
set o1.paid = now()
where o2.paid <> '0000-00-00 00:00:00'