MySQL update statement problem

Hi,

Apologies if this isn't the right place to ask - but I'm banging my head
against the wall with this one!

I'm trying to update a record in the table (creation script below) using the
following SQL statement:

UPDATE shop_customer SET eu_vat_number = "SK1234567890" AND vat_amount = 0
AND total_amount = 8.4925 WHERE customer_id = 7 AND hash="dcd5e751"

Before running the query the value for eu_vat_number is 'null', vat_amount
is 0.085 and total_amount = 8.5775.

After I run the query (mysql_affected_rows = 1) the values for vat_amount
and total_amount remain the same, but vat_number is changed to 0.

I've been looking at this most of the afternoon, and it's probably something
really simple but I just can't see it...

Any ideas?

Thanks in advance for your help...

Cheers,

Kev


-- Server version: 3.23.58
-- PHP Version: 4.3.10

CREATE TABLE `shop_customer` (
`customer_id` int(11) NOT NULL auto_increment,
`hash` varchar(8) NOT NULL default '',
`first_name` varchar(255) NOT NULL default '',
`last_name` varchar(255) NOT NULL default '',
`email_address` varchar(255) NOT NULL default '',
`member_id` int(11) default NULL,
`address1` varchar(255) default NULL,
`address2` varchar(255) default NULL,
`town` varchar(255) default NULL,
`county` varchar(255) default NULL,
`postcode` varchar(255) default NULL,
`country` int(11) NOT NULL default '0',
`eu_vat_number` varchar(15) default NULL,
`total_items` int(11) default '0',
`net_amount` float default '0',
`vat_amount` float default '0',
`shipping_amount` float default '0',
`total_amount` float default '0',
`added` datetime NOT NULL default '0000-00-00 00:00:00',
`payment_received` datetime default NULL,
PRIMARY KEY (`customer_id`)
) TYPE=MyISAM AUTO_INCREMENT=8 ;

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: MySQL update statement problem

You need to separate the SET arguments with commas, not ANDs...
It's really doing something like this:

UPDATE shop_customer SET eu_vat_number = ("SK1234567890" AND vat_amount = 0
AND total_amount = 8.4925) WHERE customer_id = 7 AND hash="dcd5e751"

("SK1234567890" AND vat_amount = 0 AND total_amount = 8.4925) evaluates to false (0) , so that's what gets set.


Kevin Davies - Bonhurst Consulting wrote:
> Hi,
>
> Apologies if this isn't the right place to ask - but I'm banging my head
> against the wall with this one!
>
> I'm trying to update a record in the table (creation script below) using the
> following SQL statement:
>
> UPDATE shop_customer SET eu_vat_number = "SK1234567890" AND vat_amount = 0
> AND total_amount = 8.4925 WHERE customer_id = 7 AND hash="dcd5e751"
>
> Before running the query the value for eu_vat_number is 'null', vat_amount
> is 0.085 and total_amount = 8.5775.
>
> After I run the query (mysql_affected_rows = 1) the values for vat_amount
> and total_amount remain the same, but vat_number is changed to 0.
>
> I've been looking at this most of the afternoon, and it's probably something
> really simple but I just can't see it...
>
> Any ideas?
>
> Thanks in advance for your help...
>
> Cheers,
>
> Kev
>
>
> -- Server version: 3.23.58
> -- PHP Version: 4.3.10
>
> CREATE TABLE `shop_customer` (
> `customer_id` int(11) NOT NULL auto_increment,
> `hash` varchar(8) NOT NULL default '',
> `first_name` varchar(255) NOT NULL default '',
> `last_name` varchar(255) NOT NULL default '',
> `email_address` varchar(255) NOT NULL default '',
> `member_id` int(11) default NULL,
> `address1` varchar(255) default NULL,
> `address2` varchar(255) default NULL,
> `town` varchar(255) default NULL,
> `county` varchar(255) default NULL,
> `postcode` varchar(255) default NULL,
> `country` int(11) NOT NULL default '0',
> `eu_vat_number` varchar(15) default NULL,
> `total_items` int(11) default '0',
> `net_amount` float default '0',
> `vat_amount` float default '0',
> `shipping_amount` float default '0',
> `total_amount` float default '0',
> `added` datetime NOT NULL default '0000-00-00 00:00:00',
> `payment_received` datetime default NULL,
> PRIMARY KEY (`customer_id`)
> ) TYPE=MyISAM AUTO_INCREMENT=8 ;
>
>

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

RE: MySQL update statement problem

Jenaro,

You're absolutely right (',' instead of 'and' - schoolboy error!). I've
obviously been staring at the screen too long! Strange that MySQL =
accepted
it as a valid statement though.

Many thanks for the quick reply.

I'm off to have a lie down! :)

Thanks and regards,

Kevin


-----Original Message-----
From: Jenaro Centeno G=F3mez [mailto:jcenteno@aldia.com.mx]=20
Sent: 04 March 2006 17:35
To: kevin.davies@bonhurst.com
Subject: Re: [PHP-DB] MySQL update statement problem

Maybe I am wrong, biut isn=B4this the rigth way to do this:

UPDATE shop_customer SET eu_vat_number =3D "SK1234567890", vat_amount =
=3D
0,total_amount =3D 8.4925=20
WHERE customer_id =3D 7 AND hash=3D"dcd5e751"


Sorry, I've not used too much MySQL but this is the rigth way in Oracle, =

PostgreSQL and SQLServer.

Hope this helps.

Kevin Davies - Bonhurst Consulting escribi=F3:

>Hi,
>
>Apologies if this isn't the right place to ask - but I'm banging my =
head
>against the wall with this one!
>
>I'm trying to update a record in the table (creation script below) =
using
the
>following SQL statement:
>
>UPDATE shop_customer SET eu_vat_number =3D "SK1234567890" AND =
vat_amount =3D 0
>AND total_amount =3D 8.4925 WHERE customer_id =3D 7 AND =
hash=3D"dcd5e751"
>
>Before running the query the value for eu_vat_number is 'null', =
vat_amount
>is 0.085 and total_amount =3D 8.5775.
>
>After I run the query (mysql_affected_rows =3D 1) the values for =
vat_amount
>and total_amount remain the same, but vat_number is changed to 0.
>
>I've been looking at this most of the afternoon, and it's probably
something
>really simple but I just can't see it...
>
>Any ideas?
>
>Thanks in advance for your help...
>
>Cheers,
>
>Kev
>
>
>-- Server version: 3.23.58
>-- PHP Version: 4.3.10
>
>CREATE TABLE `shop_customer` (
> `customer_id` int(11) NOT NULL auto_increment,
> `hash` varchar(8) NOT NULL default '',
> `first_name` varchar(255) NOT NULL default '',
> `last_name` varchar(255) NOT NULL default '',
> `email_address` varchar(255) NOT NULL default '',
> `member_id` int(11) default NULL,
> `address1` varchar(255) default NULL,
> `address2` varchar(255) default NULL,
> `town` varchar(255) default NULL,
> `county` varchar(255) default NULL,
> `postcode` varchar(255) default NULL,
> `country` int(11) NOT NULL default '0',
> `eu_vat_number` varchar(15) default NULL,
> `total_items` int(11) default '0',
> `net_amount` float default '0',
> `vat_amount` float default '0',
> `shipping_amount` float default '0',
> `total_amount` float default '0',
> `added` datetime NOT NULL default '0000-00-00 00:00:00',
> `payment_received` datetime default NULL,
> PRIMARY KEY (`customer_id`)
>) TYPE=3DMyISAM AUTO_INCREMENT=3D8 ;
>
> =20
>

--=20
L.A. Jenaro Centeno G=F3mez
Administraci=F3n de Redes y Bases de Datos
Alimentos La Concordia, S.A. de C.V.
Loma de Prados No. 1332
Col. La Marimba
Lagos de Moreno, Jal.
C.P. 47470
Tel.- 01 474 741 92 00 Ext. 9280


La informacion contenida en este mensaje y sus anexos es de caracter =
privado
y confidencial y para el uso exclusivo de la persona o institucion a la =
cual
ha sido enviado y para otros autorizados para recibirlo, por lo que no =
podra
distribuirse sin la autorizacion expresa del remitente. Si usted no es =
el
destinatario a quien este mensaje fue dirigido o si no es un empleado
responsable del envio de este mensaje al destinatario, se hace de su
conocimiento que cualquier revision, diseminacion, distribucion, copia =
u
otro uso o acto realizado con base en o relacionado con el contenido de =
este
mensaje y sus anexos est=E1 estrictamente prohibida y puede ser ilegal.
Asimismo, el presente mensaje no representa la manifestacion del
consentimiento de ninguna de las partes, por lo que no genera derecho u
obligaci=F3n alguna para ambas sino hasta que sus representantes legales =
asi
lo manifiesten por escrito. Si usted ha recibido este comunicado y sus
anexos por error, le solicitamos lo notifique inmediatamente al =
remitente
respondiendo a este correo y borre el presente y sus anexos de su =
sistema
sin conservar copia de los mismos. Se suprimieron acentos y caracteres
especiales para legibilidad del mismo. Gracias. Alimentos La Concordia,
S.A. de C.V.

The information contained in this message and its attachments is private =
and
confidential and is intended solely for the use of the individual or =
entity
to whom it is addressed and others who are authorized to receive it;
therefore, its distribution cannot be possible without authorization =
from
the sender. If you are not the intended recipient or an employee
responsible for delivering this message to the intended recipient, you =
are
hereby notified that any revision, dissemination, distribution, copying =
or
other use or action based upon or relative to the information contained =
in
this message and its attachments is strictly prohibited and may be =
unlawful.
You are also informed that the contents of this message shall not be
considered as an agreement between the parties and shall not bind any of
them until their attorneys decide to do so in writing. If you have =
received
this message and its attachments by error, please immediately notify the
sender by replying to this message and deleting it from your system =
without
keeping a copy. Thank you. Alimentos La Concordia, S.A. de C.V.

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php