UPDATE from SELECT but still return selected data in MySQL

Question

I have read a bunch of ways that has gotten me this far. But I can't get to the finish line. I have a table of coupon codes. I want to use one transaction to select the next available code, mark it as used and input the order number. I can get the update and nested select to work, but I cannot figure out how to actually return the coupon code from the select. It just returns 1 row updated.

Here's what I've got:

UPDATE `prcoupon` pr 
SET 
    `pr`.`status` = '1',
    `pr`.`invoicenumber` = '09990002'
WHERE
    `pr`.`couponCode` = (SELECT 
            `prcoupon`.`couponcode`
        FROM
            `prcoupon`
        WHERE
            `status` = 0
        LIMIT 1)

Sample data

What I need returned is: couponCode: SL2T-03A0-JVCY-W2XMXG

Answer 1

If I understand correctly, you can try to use UPDATE ... JOIN with ROW_Nunber windwon function.

UPDATE prcoupon pr 
JOIN (
  SELECT *,ROW_NUMBER() OVER(ORDER BY couponCode) rn
  FROM prcoupon
  WHERE status = 0
) t2 ON pr.couponcode = t2.couponcode
SET pr.status = 1,
    pr.invoicenumber = '09990002'
WHERE rn = 1

sqlfiddle