Re: On Duplicate Key Update question

2007-01-06 Thread ViSolve DB Team 

Hi,

From your query, understood that you want to retain old qty and new qty; 

result in another field.

Try with,

INSERT INTO TABLE1 (id,newqty) values (6,300) ON DUPLICATE KEY UPDATE 
totqty=oldqty+newqty, oldqty=newqty;


Thanks,
ViSolve DB Team

- Original Message - 
From: Ed Reed [EMAIL PROTECTED]

To: mysql@lists.mysql.com
Sent: Saturday, January 06, 2007 4:10 AM
Subject: On Duplicate Key Update question



I use On Duplicate Key Update a lot and I usually use it like this,

Insert Into tablename (myID, Qty)
Values (1034,15),
(6,13),
(5001,1),
(145,20) On Duplicate Key Update Qty=Values(Qty);
This works very well but now I'd like to do something a little
different. I'd like to have a query like this but instead of replacing
the value of the previous Qty I'd like it to take the old Qty and the
new Qty and store the sum of the two values in the Qty field.

Is this possible and can anyone tell me how?

Thanks




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

On Duplicate Key Update question

2007-01-05 Thread Ed Reed 
I use On Duplicate Key Update a lot and I usually use it like this,
 
Insert Into tablename (myID, Qty)
Values (1034,15),
(6,13),
(5001,1),
(145,20) On Duplicate Key Update Qty=Values(Qty);
This works very well but now I'd like to do something a little
different. I'd like to have a query like this but instead of replacing
the value of the previous Qty I'd like it to take the old Qty and the
new Qty and store the sum of the two values in the Qty field. 
 
Is this possible and can anyone tell me how?
 
Thanks

Re: On Duplicate Key Update question

2007-01-05 Thread Ed Reed 
Sorry for the premature question. I think I figured it out. 
 
On Duplicate Key Update Qty=Qty+Values(Qty);
I haven't tested it yet but it makes sense that it'll work.
 


 Ed Reed [EMAIL PROTECTED] 1/5/07 2:40 PM 
I use On Duplicate Key Update a lot and I usually use it like this,

Insert Into tablename (myID, Qty)
Values (1034,15),
(6,13),
(5001,1),
(145,20) On Duplicate Key Update Qty=Values(Qty);

This works very well but now I'd like to do something a little
different. I'd like to have a query like this but instead of replacing
the value of the previous Qty I'd like it to take the old Qty and the
new Qty and store the sum of the two values in the Qty field. 

Is this possible and can anyone tell me how?

Thanks

Re: On Duplicate Key Update question

2007-01-05 Thread Chris W 

Ed Reed wrote:

I use On Duplicate Key Update a lot and I usually use it like this,
 
Insert Into tablename (myID, Qty)

Values (1034,15),
(6,13),
(5001,1),
(145,20) On Duplicate Key Update Qty=Values(Qty);
This works very well but now I'd like to do something a little
different. I'd like to have a query like this but instead of replacing
the value of the previous Qty I'd like it to take the old Qty and the
new Qty and store the sum of the two values in the Qty field. 
 
Is this possible and can anyone tell me how?
 
Thanks


  

doing it one record at a time I would do something like this...

Insert Into tablename (myID, Qty)
Values ($myID,$Qyt)
On Duplicate Key Update Qty = Qty + $Qty

you may also be able to use...
On Duplicate Key Update Qty = Qty + Values(Qty)
But I have never used that before so I'm not sure





--
Chris W
KE5GIX

Protect your digital freedom and privacy, eliminate DRM, 
learn more at http://www.defectivebydesign.org/what_is_drm;


Gift Giving Made Easy
Get the gifts you want  
give the gifts they want
One stop wish list for any gift, 
from anywhere, for any occasion!

http://thewishzone.com


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]