Update MYSQL table with REGEXP .*?

Question

Sorry if the question is silly but I am very noob with MYSQL and I am trying to update my table to clean and standardise the table. So I learned how to use the UPDATE command however I just want to check if there is a way to do the following:

UPDATE parse SET Stock = REPLACE(Stock, 'ETA%', 'Sold out');

Basically I want to have all the lines in my table that contain (or start with) ETA and change it to Sold out. The data is extracted from sites so Stock column has a bunch of crap like "ETA: 19-May" "ETA: in 5 days". I just want to change this all at once to "Sold out". Is there a way to do this?

I have also tried: UPDATE parse SET Stock = IF(Stock REGEXP '^ETA', 'Sold out', Stock);

but it didn't work. Any suggestion? Thanks !

pcsutar · Accepted Answer · 2021-06-12 06:31:45Z

2

You should add condition in where clause as shown below:

UPDATE parse SET Stock = 'Sold out' WHERE Stock LIKE '%ETA%';

answered Jun 12, 2021 at 6:31

pcsutar

1,8292 gold badges13 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Tim Biegeleisen · Accepted Answer · 2021-06-12 06:33:18Z

0

You don't even need REGEXP here:

UPDATE parse SET Stock = 'Sold out' WHERE INSTR(Stock, 'ETA') > 0;

If you wanted to use REGEXP then you could use:

UPDATE parse SET Stock = 'Sold out' WHERE Stock REGEXP 'ETA';

answered Jun 12, 2021 at 6:33

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

Collectives™ on Stack Overflow

Update MYSQL table with REGEXP .*?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related