How can I change this SELECT statement into an UPDATE statement?

Question

I've managed to write a query which pulls back orders which do not have items in a separate table. I'd now like to update the xxx_orders based on the record set returned. Can it be done in one query?

SELECT Orders.OrderNumber,
       Orders.CustomerNumber,
       AffiliateCode
FROM   xxx_orders AS Orders
       LEFT OUTER JOIN xxx_items AS Items
         ON Orders.OrderNumber = Items.OrderNumber
WHERE  DATEDIFF(NOW(), OrderDate) >= 14
       AND Orders.Product = 'HOUSEHOLD'
       AND STATUS = 'BOOKING'
       AND Completed = 1
       AND Items.Id IS NULL
ORDER  BY OrderDate DESC

Are you looking for something like this? http://stackoverflow.com/questions/2334712/update-from-select-using-sql-server/2334741#2334741 — Fabian Bigler, Jun 13 '13 at 15:31

score 1 · Accepted Answer · answered Jun 13 '13 at 15:37

1

The basic syntax for updating via a JOIN in MySQL would look like this:

UPDATE xxx_orders AS Orders
LEFT OUTER JOIN xxx_items AS Items
         ON Orders.OrderNumber = Items.OrderNumber
SET  Orders.CancelledReason = 'Expired'
WHERE  DATEDIFF(NOW(), OrderDate) >= 14
       AND Orders.Product = 'HOUSEHOLD'
       AND STATUS = 'BOOKING'
       AND Completed = 1
       AND Items.Id IS NULL

Edit: SET comes before WHERE

answered Jun 13 '13 at 15:37

Hart CO

34,064
6
48
63

Thanks! When I select I get 135 rows, when I did the update it did 163 - that's gotta be a concern. I can't see where the difference would be? – James Wilson Jun 13 '13 at 15:49
That's odd, try to isolate one of the 28 'extra' rows and see if there's any reason it shouldn't be updated, it looks like I copied your criteria correctly. – Hart CO Jun 13 '13 at 15:54
Sorry my mistake. I added another JOIN to the select but no the update! Thanks – James Wilson Jun 13 '13 at 16:01

How can I change this SELECT statement into an UPDATE statement?

1 Answers1