I am having a bit of trouble with the "IN" operator. I am thinking that it just may be my misunderstanding.
Situation. I have a table (ZipCounty) that I want to pull vendors from based on zipcode. So I have a Vendor like field declared as TEXT. To keep the table up to date, I issuse updates in the manor of...
UPDATE ZipCounty SET VendorLink = IF(VendorLink IS NULL,'528',CONCAT(VendorLink,',528')) WHERE State = 'CA' AND 528 NOT IN (VendorLink);
IN() takes a list of values to look for. It does not take a value that itself consists of a list of values. In your statement above, the IN expression will be true only if the value of VendorLink is exactly 528.
Where 528 would be the ID of that particular vendor. These updates work fine, but later if I need to do another update, say to remove 528 from the VendorLink field, I can find all of the the records where 528 is the only number in the field, or if it is the first number in the field, but not if it is the last number in the field.
This sounds like you don't want VendorLink values that begin with 528 followed by either the end of the value or a comma. You might try a REGEXP pattern match such as this:
NOT (VendorLink REGEXP '^528($|,)')
Here's how the pattern works with some sample values:
+------------+-------------------------------------+ | VendorLink | NOT (VendorLink REGEXP '^528($|,)') | +------------+-------------------------------------+ | 528 | 0 | | 5289 | 1 | | 528,1 | 0 | | 5289,1 | 1 | | 1,528 | 1 | | 1,5289 | 1 | +------------+-------------------------------------+
Note that REGEXP will return NULL if VendorLink is NULL.
Hope someone can help me out.
Roger
Here are a few selects
mysql> SELECT Count(ID) FROM ZipCounty WHERE VendorLink = '529,528'; +-----------+ | Count(ID) | +-----------+ | 1384 | +-----------+ 1 row in set (0.25 sec)
mysql> SELECT Count(ID) FROM ZipCounty WHERE '528' IN (VendorLink); +-----------+ | Count(ID) | +-----------+ | 0 | +-----------+ 1 row in set (0.25 sec)
mysql> SELECT VendorLink, Count(ID) FROM ZipCounty WHERE VendorLink IS NOT NULL GROUP BY VendorLink; +------------+-----------+ | VendorLink | Count(ID) | +------------+-----------+ | 529 | 3844 | | 529,528 | 1384 | +------------+-----------+ 2 rows in set (0.61 sec)
Why do I get a count of 0 for the Second Query?
Here is a list of all the queries and output. (redhat 9.0 version 2.23.54)
This also happens on the latest Windows version (mysql Ver 12.21 Distrib 4.0.14, for Win95/Win98 (i32))
mysql> \s -------------- mysql Ver 11.18 Distrib 3.23.54, for redhat-linux-gnu (i386)
Connection id: 59756 Current database: TPZ Current user: [EMAIL PROTECTED] Current pager: stdout Using outfile: '' Server version: 3.23.54 Protocol version: 10 Connection: Localhost via UNIX socket Client characterset: latin1 Server characterset: latin1 UNIX socket: /var/lib/mysql/mysql.sock Uptime: 12 days 19 hours 38 min 36 sec
Threads: 2 Questions: 2354432 Slow queries: 43 Opens: 143 Flush tables: 1 Open tables: 64 Queries per second avg: 2.126 --------------
mysql> SHOW CREATE TABLE ZipCounty; +-----------+--------------------------------------------------------------- ---------------------------------------------------------------------------- ---------------------------------------------------------------------------- ---------------------------------------------------------------------------- ---------------------------------------------------------------------------- ------------------------+ | Table | Create Table | +-----------+--------------------------------------------------------------- ---------------------------------------------------------------------------- ---------------------------------------------------------------------------- ---------------------------------------------------------------------------- ---------------------------------------------------------------------------- ------------------------+ | ZipCounty | CREATE TABLE `ZipCounty` ( `ID` int(11) NOT NULL auto_increment, `City` varchar(50) default NULL, `State` char(2) default NULL, `Zip` varchar(10) default NULL, `AreaCode` char(3) default NULL, `County` varchar(50) default NULL, `VendorLink` text, `OtherLink` text, PRIMARY KEY (`ID`), KEY `Zip` (`Zip`), KEY `County` (`County`), KEY `State` (`State`) ) TYPE=MyISAM | +-----------+--------------------------------------------------------------- ---------------------------------------------------------------------------- ---------------------------------------------------------------------------- ---------------------------------------------------------------------------- ---------------------------------------------------------------------------- ------------------------+ 1 row in set (0.00 sec)
mysql> UPDATE ZipCounty SET VendorLink = NULL; Query OK, 11193 rows affected (0.99 sec) Rows matched: 69802 Changed: 11193 Warnings: 0
mysql> UPDATE ZipCounty SET VendorLink = IF(VendorLink IS NULL,'529',CONCAT(VendorLink,',529')) WHERE State = 'AL' AND 529 NOT IN (VendorLink); Query OK, 1312 rows affected (0.08 sec) Rows matched: 1312 Changed: 1312 Warnings: 0
mysql> UPDATE ZipCounty SET VendorLink = IF(VendorLink IS NULL,'529',CONCAT(VendorLink,',529')) WHERE State = 'AR' AND 529 NOT IN (VendorLink); Query OK, 1313 rows affected (0.07 sec) Rows matched: 1313 Changed: 1313 Warnings: 0
mysql> UPDATE ZipCounty SET VendorLink = IF(VendorLink IS NULL,'529',CONCAT(VendorLink,',529')) WHERE County = 'Kent' AND State = 'DE' AND 529 NOT IN (VendorLink); Query OK, 30 rows affected (0.00 sec) Rows matched: 30 Changed: 30 Warnings: 0
mysql> UPDATE ZipCounty SET VendorLink = IF(VendorLink IS NULL,'529',CONCAT(VendorLink,',529')) WHERE County = 'New Castle' AND State = 'DE' AND 529 NOT IN (VendorLink); Query OK, 70 rows affected (0.00 sec) Rows matched: 70 Changed: 70 Warnings: 0
mysql> UPDATE ZipCounty SET VendorLink = IF(VendorLink IS NULL,'529',CONCAT(VendorLink,',529')) WHERE State = 'FL' AND 529 NOT IN (VendorLink); Query OK, 2503 rows affected (0.14 sec) Rows matched: 2503 Changed: 2503 Warnings: 0
mysql> UPDATE ZipCounty SET VendorLink = IF(VendorLink IS NULL,'528',CONCAT(VendorLink,',528')) WHERE County = 'Autauga' AND State = 'AL' AND 528 NOT IN (VendorLink); Query OK, 15 rows affected (0.00 sec) Rows matched: 15 Changed: 15 Warnings: 0
mysql> UPDATE ZipCounty SET VendorLink = IF(VendorLink IS NULL,'528',CONCAT(VendorLink,',528')) WHERE County = 'Baldwin' AND State = 'AL' AND 528 NOT IN (VendorLink); Query OK, 40 rows affected (0.00 sec) Rows matched: 40 Changed: 40 Warnings: 0
mysql> UPDATE ZipCounty SET VendorLink = IF(VendorLink IS NULL,'528',CONCAT(VendorLink,',528')) WHERE County = 'Barbour' AND State = 'AL' AND 528 NOT IN (VendorLink); Query OK, 7 rows affected (0.01 sec) Rows matched: 7 Changed: 7 Warnings: 0
mysql> UPDATE ZipCounty SET VendorLink = IF(VendorLink IS NULL,'528',CONCAT(VendorLink,',528')) WHERE County = 'Bibb' AND State = 'AL' AND 528 NOT IN (VendorLink); Query OK, 9 rows affected (0.00 sec) Rows matched: 9 Changed: 9 Warnings: 0
mysql> UPDATE ZipCounty SET VendorLink = IF(VendorLink IS NULL,'528',CONCAT(VendorLink,',528')) WHERE State = 'AR' AND 528 NOT IN (VendorLink); Query OK, 1313 rows affected (0.09 sec) Rows matched: 1313 Changed: 1313 Warnings: 0
mysql> UPDATE ZipCounty SET VendorLink = IF(VendorLink IS NULL,'528',CONCAT(VendorLink,',528')) WHERE State = 'CA' AND 528 NOT IN (VendorLink); Query OK, 3878 rows affected (0.20 sec) Rows matched: 3878 Changed: 3878 Warnings: 0
mysql> UPDATE ZipCounty SET VendorLink = IF(VendorLink IS NULL,'528',CONCAT(VendorLink,',528')) WHERE State = 'IN' AND 528 NOT IN (VendorLink); Query OK, 2062 rows affected (0.09 sec) Rows matched: 2062 Changed: 2062 Warnings: 0
mysql> UPDATE ZipCounty SET VendorLink = IF(VendorLink IS NULL,'528',CONCAT(VendorLink,',528')) WHERE County = 'Brown' AND State = 'SD' AND 528 NOT IN (VendorLink); Query OK, 25 rows affected (0.00 sec) Rows matched: 25 Changed: 25 Warnings: 0
mysql> SELECT Count(ID) FROM ZipCounty WHERE 529 IN (VendorLink); +-----------+ | Count(ID) | +-----------+ | 5228 | +-----------+ 1 row in set (0.28 sec)
mysql> SELECT Count(ID) FROM ZipCounty WHERE VendorLink = '529'; +-----------+ | Count(ID) | +-----------+ | 3844 | +-----------+ 1 row in set (0.26 sec)
mysql> SELECT Count(ID) FROM ZipCounty WHERE 528 IN (VendorLink); +-----------+ | Count(ID) | +-----------+ | 5965 | +-----------+ 1 row in set (0.28 sec)
mysql> SELECT Count(ID) FROM ZipCounty WHERE VendorLink = '528'; +-----------+ | Count(ID) | +-----------+ | 5965 | +-----------+ 1 row in set (0.26 sec)
mysql> SELECT Count(ID) FROM ZipCounty WHERE VendorLink = '529,528'; +-----------+ | Count(ID) | +-----------+ | 1384 | +-----------+ 1 row in set (0.27 sec)
mysql> SELECT COUNT(ID) FROM ZipCounty WHERE VendorLink IS NOT NULL; +-----------+ | COUNT(ID) | +-----------+ | 11193 | +-----------+ 1 row in set (0.28 sec)
mysql> SELECT VendorLink, Count(ID) FROM ZipCounty WHERE VendorLink IS NOT NULL GROUP BY VendorLink; +------------+-----------+ | VendorLink | Count(ID) | +------------+-----------+ | 528 | 5965 | | 529 | 3844 | | 529,528 | 1384 | +------------+-----------+ 3 rows in set (1.04 sec)
mysql> SELECT Count(ID) FROM ZipCounty WHERE '528' IN (VendorLink); +-----------+ | Count(ID) | +-----------+ | 5965 | +-----------+ 1 row in set (0.30 sec)
mysql> UPDATE ZipCounty SET VendorLink = NULL WHERE VendorLink = '528'; Query OK, 5965 rows affected (0.72 sec) Rows matched: 5965 Changed: 5965 Warnings: 0
mysql> SELECT Count(ID) FROM ZipCounty WHERE VendorLink = '529,528'; +-----------+ | Count(ID) | +-----------+ | 1384 | +-----------+ 1 row in set (0.25 sec)
mysql> SELECT Count(ID) FROM ZipCounty WHERE '528' IN (VendorLink); +-----------+ | Count(ID) | +-----------+ | 0 | +-----------+ 1 row in set (0.25 sec)
mysql> SELECT VendorLink, Count(ID) FROM ZipCounty WHERE VendorLink IS NOT NULL GROUP BY VendorLink; +------------+-----------+ | VendorLink | Count(ID) | +------------+-----------+ | 529 | 3844 | | 529,528 | 1384 | +------------+-----------+ 2 rows in set (0.61 sec) ---
--- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.512 / Virus Database: 309 - Release Date: 8/19/2003
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
-- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com
Are you MySQL certified? http://www.mysql.com/certification/
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
