Is there a better way to write this query?

Question

I am trying to search and update a record that starts with a partial number or falls with a range.

I made a query that works where values ​​start with a specific number. I can't figure out how to do this with a range.

For example, I want to update the following values ​​in products_ean with ""

255 201-230 ---> starts with 201,202,203 ...230 236 980-990 ---> starts with 980.981,982 ...990 

I wrote the following query, which works, but not sure if it is espcialy efficient when it needs to process more than 100 thousand records. It does not work with a range.

 UPDATE products SET products_ean ="" where products_ean like "200%" OR products_ean like "020%" OR products_ean like "023%" OR products_ean like "027%" OR products_ean like "042%" OR products_ean like "221%" OR products_ean like "209%" OR products_ean like "041%" OR products_ean like "049%" OR products_ean like "026%" OR products_ean like "025%" OR products_ean like "299%"; 

Answer 1

In any case, it will be a full table scan, so you can use the products_ean function without losing performance. In doing so, you can get the request more readable, but probably not much faster. However, you can still try whether it will be faster, take the three leading numbers and compare them:

 UPDATE products SET products_ean = '' where left(products_ean,3) in ('200', '020', '027', ...); 

If you find it more readable, you can even use ranges:

 UPDATE products SET products_ean = '' where left(products_ean,3) = '255' or left(products_ean,3) between '201' and '230' ... 

Answer 2

Perhaps you could try that you are not sure about the performance, but less code .. if your ean is a string field. you can try this

 UPDATE products SET products_ean ="" where (left(products_ean,3) between 201 and 230) or (left(products_ean,3) between 980 and 990) or 

PS: you may need to select a field.

Answer 3

I do not think it will be faster, but its another alternative:

To find:

 SELECT * FROM products WHERE products_ean REGEXP '^(200|020|023|027|042|221|209|041|049|026|025|299)' 

Replace:

 UPDATE products SET products_ean = '' WHERE products_ean REGEXP '^(200|020|023|027|042|221|209|041|049|026|025|299)' 

Note: it will not use indexes

Answer 4

You can try this

 UPDATE products SET products_ean = " " WHERE STRCMP(products_ean, '200') >= 0 AND STRCMP(products_ean, '220') <= 0 

Answer 5

you can try where it is contained, it should be like:

 WHERE CONTAINS(t.products_ean, '"200*" OR "020*" OR "023*"')