Good day.

Faced a problem - I searched in Google - I did not find it, rather, because of the incorrect formulation of the question. Actually the question itself.

Table for 1000 rows. You need to edit the value of the "Description" column.

Text values ​​are stored in this column: one, two, three, five, forty, nine.
How to delete IMMEDIATELY all values ​​from ALL Lines of this COLUMN, EXCEPT the values: two, nine?

Thank you all very much!

  • one
    a column has only one value. what you ask for sql is irrelevant. - Yura Ivanov
  • I don't mean? Ie this is all my dream, and I have no DB, no columns, no rows?) The site is based on the dle engine, uses mysql to store data, I use Navikat as a client, if this simplifies the solution - Mikhail1313
  • @ Mihail1313, "simplify the solution path" bringing your structure to the 3rd normal form, @Alex Krass said everything correctly. And with such a structure, it is necessary to google "removal of the glands through ...", in the sense it is possible, but the answer to such a question does not benefit the novice programmers, it only breeds unfortunate surgeons ... - Yura Ivanov

2 answers 2

UPDATE (table name) SET (column name) = 0 WHERE (column name)! = '2.9';

  • UPDATE dle_post SET tags = 0 WHERE tags! = '1979,1984,1992,1993,1994,1995,1997,1998,1999,2001,2002,2003,2004,2005,2006,2007,2008,2009,2010,2011 , 2012,2013,2014, Australia, Austria, Belgium, Bulgaria, Bosnia-Herzegovina, Great Britain, Hungary, Germany, Hong Kong, Denmark, India, Indonesia, Ireland, Spain, Italy, Canada, China, Luxembourg, Mexico, New Zealand, Norway, United Arab Emirates, Poland, Russia, Romania, Serbia, USA, Taiwan, Tunisia, Turkey, Ukraine, France, Czech Republic, Switzerland, Sweden, South Africa, Japan '; I did, but everything went away , only zeros were left - Mikhail1313
  • What you indicated should be preserved and the rest deleted? - cyber_bober
  • yes, that indicated, should be preserved, and everything else must be removed - Mikhail1313
  • ... WHERE tags NOT IN (1979,1984,1992 ...) - cyber_bober
  • one
    @cyber_bober, as far as I understood, yes, he has there in one cell a list of values, separated by commas . - Alex Krass

I can only sympathize with you.

When building a database there is such a thing as normal forms. If they are ignored, fetching or modifying data results in logical errors. So, your base is not satisfied even with the very first (basic) normal form, which says: each cell contains exactly one logical value and nothing else. And since you pushed several logical values ​​there, it will either be difficult or impossible to manipulate data through SQL.

In this case, you will need to build a fairly complex query if you want to do this only through SQL or even use the procedural SQL / PSM extension (mysql stored procedures). But it is better to break it into three simple requests. But in this case, I can not guarantee the correct result, as there may be problems with splicing of the required tags.

UPDATE %Table% SET %Row% = 'два, девять' WHERE %Row% LIKE '%два%' AND %Row% LIKE '%девять%'; UPDATE %Table% SET %Row% = 'два' WHERE %Row% LIKE '%два%' AND %Row% NOT LIKE '%девять%'; UPDATE %Table% SET %Row% = 'девять' WHERE %Row% NOT LIKE '%два%' AND %Row% LIKE '%девять%';

PS The main problem in this variant is that the search pattern% nine% is also suitable for tags "forty nine", "nine hundred", etc. where there is a mention of the nine.