0

I am really new to mysql and might need your help. I have a a table called localgames and I am trying to replace some values to clean the table so here is the doubt and what I have tried and the proposed "solution":

To replace a simple entry that was easy, I used:

UPDATE localgames 
   SET Stock = REPLACE(Stock, '0 in stock', 'Sold out');

So all 0 in stock entries in the column Stock where replaced by Sold out. Cool.

However now I have another entries for example 1 in stock, 2 in stock, 3 in stock and more 100 of XX in stock and I want to replace them all for In stock.

Is there a way to use something like SET Stock = REPLACE(Stock, '*.* in stock', 'In Stock');?

Priya
  • 334
  • 3
  • 8
Rafael Souza
  • 31
  • 6

1 Answers1

0

Use the all mighty CASE syntax:

UPDATE inventory
SET quantity = CASE WHEN quantity = '0 in stock' THEN 'Out of Stock' ELSE 'In Stock' END

See my SQLfiddle here: http://sqlfiddle.com/#!9/2494c6/1

Alternatively, IF works as well:

UPDATE inventory
SET quantity = IF(quantity = '0 in stock', 'Out of stock', 'In Stock')

SQLfiddle here: http://sqlfiddle.com/#!9/4551a8/1

Update: In response to your comment, another 2 ways (If you have REGEXP support)

UPDATE inventory
SET quantity = CASE WHEN quantity = '0 in stock' THEN 'Out of Stock'
  WHEN quantity REGEXP '^[0-9]+ in stock$' THEN 'In Stock'
  ELSE quantity END

OR simply

UPDATE inventory
SET quantity = IF(quantity REGEXP '^[0-9]+ in stock$', 'In Stock', quantity)

Update 2: If you don't have regexp support, you can still do this (Albeit in a hacky way):

Assuming you've already converted the 0 in stock to out of stock, you can use this:

UPDATE inventory
SET quantity = IF(
    CONCAT('', SUBSTR(quantity, 1, 1) * 1) = SUBSTR(quantity, 1, 1),
    'In Stock',
    quantity
)

I used this post for determining if something is a number, and I'm using SUBSTR(quantity, 1, 1) to determine if the first letter in the string is a number, and if so, setting it. Be aware This will not work, if you have things like 1 in backorder.

See the SQLfiddle here: http://sqlfiddle.com/#!9/1774c6/1

Blue
  • 22,608
  • 7
  • 62
  • 92
  • Tanks for answer. The thing is: I have other things listed in the Stock field like Pre-Order, Backorder etc. Not only "In Stock" and "Sold out". :( Any other clues? Cheers – Rafael Souza Jul 15 '18 at 07:45
  • @RafaelSouza Just keep using the CASE statement, to define everything. – Blue Jul 15 '18 at 07:47
  • @RafaelSouza Also, what's your DB version? Percona/Mariadb have some extra functionality that may help. – Blue Jul 15 '18 at 07:47
  • Depending on your DB version, and if you have REGEXP support (Percona/Mariadb have it, and later versions of MySQL), you can use that. – Blue Jul 15 '18 at 07:54
  • lol, which did you use, curious @RafaelSouza? This was fun times doing research. – Blue Jul 15 '18 at 08:02
  • Fantastic answer! So just to be able to complete my task, I have other text like "Pre-order - out 22 Jun 2018 (estimated date)" or "Pre-order - out 25 Jun 2018" etc and I want to transform all in "Pre-Order" only. Is there a way to do this? Thanks heaps! – Rafael Souza Jul 15 '18 at 08:05
  • mysql version 5.7 – Rafael Souza Jul 15 '18 at 08:06
  • `quantity REGEXP '^Pre-order'` should match where the field starts with `Pre-order` – Blue Jul 15 '18 at 08:06
  • @FranferZ everything else worked like a charm! Since I am very new to mysql do you mind to exemplify how this quantity REGEXP '^Pre-order' works? So I need to update the field Stock to replace all "Pre-Order something" to "Pre-Order. Thank you so much! – Rafael Souza Jul 15 '18 at 08:24
  • Oh sorry, just adding a last one: also I would like to replace all blanks to "Stock unk" do you think this will work? UPDATE localgames SET Stock = REPLACE(Stock, '', 'Stock unk'); – Rafael Souza Jul 15 '18 at 08:25
  • `UPDATE localgames SET Stock = IF(Stock = '', 'Stock Unknown', Stock);`. – Blue Jul 15 '18 at 08:30
  • You're a genius! What about this one? "Since I am very new to mysql do you mind to exemplify how this quantity REGEXP '^Pre-order' works? So I need to update the field Stock to replace all "Pre-Order something" to "Pre-Order. Thank you so much! " – Rafael Souza Jul 15 '18 at 08:40
  • yes indeed I said last one, but this question was there before the "last one" :) – Rafael Souza Jul 15 '18 at 08:50
  • `quantity REGEXP '^Pre-order'` is simply a conditional (Will return true or false). REGEXP is regular expression, and it has a very unique but very powerful syntax for matching patterns of strings. `UPDATE localgames SET Stock = IF("The Stock starts with Preorder (This should be true or false)", "What to set it to if Stock does start with Pre-order", "What to set it to if Stock does not start with Pre-order")`. Take a look at my previous examples. – Blue Jul 15 '18 at 08:50
  • ok I got it, so do you think that this should work? I don't want to set anything "if stock do not start with Pre-order" so I did this: UPDATE localgames SET Stock = IF("^Pre-order", "Pre-order"); do you think this is the way to go? Cheers – Rafael Souza Jul 15 '18 at 09:08
  • `IF` takes 3 parameters, you can't simply omit one. (Hint: Setting `Stock` to `Stock` is essentially leaving it the same). #2: "^Pre-order" is a string, it's not a boolean. (Again, take a look at my other examples) – Blue Jul 15 '18 at 09:09
  • What about this? UPDATE localgames SET Stock = IF(Stock REGEXP '^Pre-order', 'Pre-order', Stock) – Rafael Souza Jul 15 '18 at 09:15
  • Yup! Also, slight formatting note (Use backticks `\`` around code, to format them nicely in comments). – Blue Jul 15 '18 at 09:16