From a MySQL table select one or more rows and update columns from each one

Question

I have a table with pre-existing giveaway codes and I need to select one or more rows and then update three columns of each row with personal identification, customer code, and "reserved" status. This to reserve each row until receiving a response from our client's API.

The table look like this:

code               identification    customer_code    status
-----------------------------------------------------------------
81Ow3tCs1nNwxKu    --                --               available
I1NdH9F22S7RhU3    --                --               available
Xc942LWe8Z6nt8x    --                --               available
zcLMRO8kSeM7S06    --                --               available
K94erORvzSsU0ik    --                --               available

Tried with this but got an error:

UPDATE promo_codes 
SET 
    identification='12345',
    customer_code='67890',
    status='reserved' 
FROM 
(SELECT code FROM promo_codes WHERE status='available' LIMIT 2);

Then I tried with REPLACE INTO but also with error:

REPLACE INTO promo_codes(identification,customer_code,status)
VALUES('12345','67890','reserved')
WHERE
(SELECT code FROM promo_codes WHERE status='available' LIMIT 2);

I do not know what else to do. Could someone give me an idea? Thank you very much for the help.

score 1 · Accepted Answer · answered Aug 14 '20 at 12:15

A little rewriting and you code works

You should consider adding a ORDER BY RAND() , because a LIMIT without an order is quite meaningless

CREATE TABLE promo_codes  (
  `code` VARCHAR(15),
  `identification` VARCHAR(20),
  `customer_code` VARCHAR(20),
  `status` VARCHAR(9)
);

INSERT INTO promo_codes 
  (`code`, `identification`, `customer_code`, `status`)
VALUES
  ('81Ow3tCs1nNwxKu', '--', '--', 'available'),
  ('I1NdH9F22S7RhU3', '--', '--', 'available'),
  ('Xc942LWe8Z6nt8x', '--', '--', 'available'),
  ('zcLMRO8kSeM7S06', '--', '--', 'available'),
  ('K94erORvzSsU0ik', '--', '--', 'available');

UPDATE promo_codes 
SET 
    identification='12345',
    customer_code='67890',
    status='reserved' 
WHERE status='available' LIMIT 2;

SELECT * FROM promo_codes

code            | identification | customer_code | status   
:-------------- | :------------- | :------------ | :--------
81Ow3tCs1nNwxKu | 12345          | 67890         | reserved 
I1NdH9F22S7RhU3 | 12345          | 67890         | reserved 
Xc942LWe8Z6nt8x | --             | --            | available
zcLMRO8kSeM7S06 | --             | --            | available
K94erORvzSsU0ik | --             | --            | available

db<>fiddle here

From a MySQL table select one or more rows and update columns from each one

1 Answers1