Re: problem with update statement
That is very odd behavior and shouldn't be happening. I ran a quick test on my machine and MySQL does not exhibit that behavior. You may try running and repair on your table. Perhaps something is out of whack. What version of MySQL are you running? On Apr 22, 2005, at 6:08 PM, [EMAIL PROTECTED] wrote: Hello, all: In a MyISAM table, I have a column named MAC, of type VARCHAR(17). This field is used to hold MAC addresses of computers' network interface cards. These MAC addresses are in the form XX:XX:XX:XX:XX:XX, where X can be either a number or an uppercase letter. I can run select * from table where MAC='00:04:FB:23:5A:44' and the correct record is returned. However, performing update table set port_index='123' where MAC='00:04:FB:23:5A:44' does not work as I expected. It does update the correct record, but also updates all other records whose MAC field is empty. When I originally created this field, I used type VARCHAR (17), null, default value NULL. In troubleshooting this, I have since change the type to CHAR(17), not null, empty default value. Still have the same problem. Does anyone know what's going on here, and how I could correct this? Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: problem with update statement
Hi,
this is an example where what you describe doesn't happen (v 4.1 and 5.0) :
mysql create table ports(mac varchar(17),port_index varchar(3));
Query OK, 0 rows affected (0.10 sec)
mysql insert into ports values('00:04:FB:23:5A:44','120'),(NULL,'120');
Query OK, 2 rows affected (0.08 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql select * from ports;
+---++
| mac | port_index |
+---++
| 00:04:FB:23:5A:44 | 120|
| NULL | 120|
+---++
2 rows in set (0.00 sec)
mysql select * from ports where mac='00:04:FB:23:5A:44';
+---++
| mac | port_index |
+---++
| 00:04:FB:23:5A:44 | 120|
+---++
1 row in set (0.00 sec)
mysql
mysql update ports set port_index='123' where mac='00:04:FB:23:5A:44';
Query OK, 1 row affected (0.41 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql select * from ports;
+---++
| mac | port_index |
+---++
| 00:04:FB:23:5A:44 | 123|
| NULL | 120|
+---++
2 rows in set (0.00 sec)
mysql
if you use an old version which is buggy, just migrate.
Mathias
Selon Brent Baisley [EMAIL PROTECTED]:
That is very odd behavior and shouldn't be happening. I ran a quick
test on my machine and MySQL does not exhibit that behavior. You may
try running and repair on your table. Perhaps something is out of
whack. What version of MySQL are you running?
On Apr 22, 2005, at 6:08 PM, [EMAIL PROTECTED] wrote:
Hello, all:
In a MyISAM table, I have a column named MAC, of type VARCHAR(17).
This field is used to hold MAC addresses of computers' network
interface cards. These MAC addresses are in the
form XX:XX:XX:XX:XX:XX, where X can be either a number or an
uppercase letter. I can run select * from table where
MAC='00:04:FB:23:5A:44' and the correct record is returned. However,
performing update table set port_index='123' where
MAC='00:04:FB:23:5A:44' does not work as I expected. It does update
the correct record, but also updates all other records whose MAC field
is empty. When I originally created this field, I used type VARCHAR
(17), null, default value NULL. In troubleshooting this, I have since
change the type to CHAR(17), not null, empty default value. Still
have the same problem. Does anyone know what's going on here, and how
I could correct this?
Thanks!
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
Brent Baisley
Systems Architect
Landover Associates, Inc.
Search Advisory Services for Advanced Technology Environments
p: 212.759.6400/800.759.0577
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: problem with update statement
Thanks for the info. I am using 4.0.24, so maybe this is the problem.
I was able to get around this by first checking if the mac field was
empty, but I will look into upgrading mysql to a more current version.
thanks
- Original Message -
From: [EMAIL PROTECTED]
Date: Monday, April 25, 2005 9:26 am
Subject: Re: problem with update statement
Hi,
this is an example where what you describe doesn't happen (v 4.1
and 5.0) :
mysql create table ports(mac varchar(17),port_index varchar(3));
Query OK, 0 rows affected (0.10 sec)
mysql insert into ports
values('00:04:FB:23:5A:44','120'),(NULL,'120');Query OK, 2 rows
affected (0.08 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql select * from ports;
+---++
| mac | port_index |
+---++
| 00:04:FB:23:5A:44 | 120|
| NULL | 120|
+---++
2 rows in set (0.00 sec)
mysql select * from ports where mac='00:04:FB:23:5A:44';
+---++
| mac | port_index |
+---++
| 00:04:FB:23:5A:44 | 120|
+---++
1 row in set (0.00 sec)
mysql
mysql update ports set port_index='123' where
mac='00:04:FB:23:5A:44';Query OK, 1 row affected (0.41 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql select * from ports;
+---++
| mac | port_index |
+---++
| 00:04:FB:23:5A:44 | 123|
| NULL | 120|
+---++
2 rows in set (0.00 sec)
mysql
if you use an old version which is buggy, just migrate.
Mathias
Selon Brent Baisley [EMAIL PROTECTED]:
That is very odd behavior and shouldn't be happening. I ran a quick
test on my machine and MySQL does not exhibit that behavior. You
may
try running and repair on your table. Perhaps something is out of
whack. What version of MySQL are you running?
On Apr 22, 2005, at 6:08 PM, [EMAIL PROTECTED] wrote:
Hello, all:
In a MyISAM table, I have a column named MAC, of type
VARCHAR(17). This field is used to hold MAC addresses of
computers' network
interface cards. These MAC addresses are in the
form XX:XX:XX:XX:XX:XX, where X can be either a number or an
uppercase letter. I can run select * from table where
MAC='00:04:FB:23:5A:44' and the correct record is returned.
However, performing update table set port_index='123' where
MAC='00:04:FB:23:5A:44' does not work as I expected. It does
update the correct record, but also updates all other records
whose MAC field
is empty. When I originally created this field, I used type
VARCHAR (17), null, default value NULL. In troubleshooting
this, I have since
change the type to CHAR(17), not null, empty default value.
Still
have the same problem. Does anyone know what's going on here,
and how
I could correct this?
Thanks!
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
Brent Baisley
Systems Architect
Landover Associates, Inc.
Search Advisory Services for Advanced Technology Environments
p: 212.759.6400/800.759.0577
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
problem with update statement
Hello, all: In a MyISAM table, I have a column named MAC, of type VARCHAR(17). This field is used to hold MAC addresses of computers' network interface cards. These MAC addresses are in the form XX:XX:XX:XX:XX:XX, where X can be either a number or an uppercase letter. I can run select * from table where MAC='00:04:FB:23:5A:44' and the correct record is returned. However, performing update table set port_index='123' where MAC='00:04:FB:23:5A:44' does not work as I expected. It does update the correct record, but also updates all other records whose MAC field is empty. When I originally created this field, I used type VARCHAR (17), null, default value NULL. In troubleshooting this, I have since change the type to CHAR(17), not null, empty default value. Still have the same problem. Does anyone know what's going on here, and how I could correct this? Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Problem with UPDATE statement
In what way is it mangling my query? and why won't the same query work from my php script. I would accept this if it was just any interface but myphpadmin is supposed to be in line with mysql standards. update cdrequest set albumfill = hot shots II, releasedatefill = 20010708 where requstID = 2; i get: You have an error in your SQL syntax near '\hot shots II\, releasedatefill = 20010708 where requstID = 2' at line 1 I can't see any reason for the error. Your web based interface is mangling your query. part 2: when I flip to properties for the cdrequest just to make sure i'm spelling the columns corectly and cut and paste the exact same query on that page IT WORKS Why would it work on that page but not the other. both are supposed to be places where you can enter a normal sql query.. Because your web based interface is mangling your query. Try using the mysql command line client. It also doesn't work in my PHP script which is where I really want it to work. Paste source. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Problem with UPDATE statement
On Fri, Aug 10, 2001 at 02:19:21AM -0400, Julian Simpson wrote: In what way is it mangling my query? and why won't the same query work from my php script. I would accept this if it was just any interface but myphpadmin is supposed to be in line with mysql standards. update cdrequest set albumfill = hot shots II, releasedatefill = 20010708 where requstID = 2; i get: You have an error in your SQL syntax near '\hot shots II\, releasedatefill = 20010708 where requstID = 2' at line 1 I can't see any reason for the error. Your web based interface is mangling your query. Hi Julian. I'd recommend taking the suggestion and try the query through the command-line interface. If it works there, then you've isolated the problem to the interface you're using. Divide and conquer. BTW, should requstID be requestID? -- Maurice Aubrey [EMAIL PROTECTED] Watch the costs and the profits will follow. - Andrew Carnegie MySQL 3.22.32: up 21.9 days, processed 1,514,328,327 queries (799/sec. avg.) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Problem with UPDATE statement
In what way is it mangling my query? and why won't the same query work from my php script. I would accept this if it was just any interface but myphpadmin is supposed to be in line with mysql standards. update cdrequest set albumfill = hot shots II, releasedatefill = 20010708 where requstID = 2; i get: You have an error in your SQL syntax near '\hot shots II\, releasedatefill = 20010708 where requstID = 2' at line 1 I can't see any reason for the error. Your web based interface is mangling your query. Hi Julian. I'd recommend taking the suggestion and try the query through the command-line interface. If it works there, then you've isolated the problem to the interface you're using. Divide and conquer. At this point I don't have telnet access on my host so I'm down to using scripts and myphpadmin. I've made a request to get telnet access and I'll let you know if command line helpsIn the meantime I'm going to try whatever mysql interfaces i can get my hands on. But it's my php script that Is the main problem. Even If command line does what it should and responds accordingly to a normal insert statement. It still doesn't help me to use it where i need itIn my code BTW, should requstID be requestID? Fortunately this was a typo i made only for the benefit of this group. rest assured I haven't been testing with a misspelled column name :) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Problem with UPDATE statement
On 10-Aug-2001 Julian Simpson wrote: In what way is it mangling my query? and why won't the same query work from my php script. I would accept this if it was just any interface but myphpadmin is supposed to be in line with mysql standards. update cdrequest set albumfill = hot shots II, releasedatefill ^ here ^ and here use single quotes : $qry=update cdrequest set albumfill = 'hot shots II', ... Regards, -- Don Read [EMAIL PROTECTED] -- It's always darkest before the dawn. So if you are going to steal the neighbor's newspaper, that's the time to do it. (53kr33t w0rdz: sql table query) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Problem with UPDATE statement
I have a table 'cdrequest' it has columns 'albumfill' and 'releasedatefill' among others when i do the following query with myphpadmin update cdrequest set albumfill = hot shots II, releasedatefill = 20010708 where requstID = 2; i get: You have an error in your SQL syntax near '\hot shots II\, releasedatefill = 20010708 where requstID = 2' at line 1 I can't see any reason for the error. part 2: when I flip to properties for the cdrequest just to make sure i'm spelling the columns corectly and cut and paste the exact same query on that page IT WORKS Why would it work on that page but not the other. both are supposed to be places where you can enter a normal sql query.. It also doesn't work in my PHP script which is where I really want it to work. What am I missing?? thanx Julian btw. I've tried all different combinations of double quotes and single quotes so it's not that - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Problem with UPDATE statement
update cdrequest set albumfill = hot shots II, releasedatefill = 20010708 where requstID = 2; i get: You have an error in your SQL syntax near '\hot shots II\, releasedatefill = 20010708 where requstID = 2' at line 1 I can't see any reason for the error. Your web based interface is mangling your query. part 2: when I flip to properties for the cdrequest just to make sure i'm spelling the columns corectly and cut and paste the exact same query on that page IT WORKS Why would it work on that page but not the other. both are supposed to be places where you can enter a normal sql query.. Because your web based interface is mangling your query. Try using the mysql command line client. It also doesn't work in my PHP script which is where I really want it to work. Paste source. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
