Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: Anders Altberg
  Where is Anders Altberg?
 Uppsala
 Sweden
 Anders Altberg
 To: joe Degorio
  Where is joe Degorio?
 Tacloban City
 Philippines
 joe Degorio
 Tags
Subject: RE: help with SQL command
Thread ID: 209381 Message ID: 209437 # Views: 1 # Ratings: 0
Version: Visual FoxPro 8 Category: Databases, Tables and SQL Server
Date: Sunday, December 28, 2008 8:39:35 PM         
   


> Hello Everyone,
> Can anybody share an idea\code to process with the following data sample...
>
>
> I would like to subtract the total Quantities of the same item product ID (e.g expiry_date.itemprod_id=4800289820865 which has the total of 5 qty let say I want to subtract 4) but must start deducting from the priority date 08/12/28. The expected result should be.....
> ****Itemprod_id************qty*******expiry*******lot_no
> รง***********1********09/01/10*****WING1000
>
> thanks in advance,
> joe

Updating or deleting a single given row with the SQL UPDATE or SQL DELETE commands is only possible if the row can be uniquly identified in a WHERE clause.
That would require the table to have unique key, a Primary Key, or a some combination of values that is quaranteed to form a unique key taken together.
There is no such key in this table so what you are asking is not possible.
If I want e g to deduct 1 from qty where the group = 4800289820865 AN D the Expiry date = 09/01/10 (which by the way is a very ambiguous date, possible Jan 10 2009 or Sep 1 2010 or Jan 09 2010), is that particular date guaranteed to only occur once in the group? I believe not.
If there's a Primary Key then one can let the user select a row and update qty in that row : WHERE pkey= uniquevalue.

The Xbase commands REPLACE IN alias and DELETE IN alias will act on the 'current row' only, but the Sql commands UPDATE tablename and DELETE FROM tablename have no idea of a 'current row', so they need a unique key in a WHERE clause.
-Anders

ENTIRE THREAD

help with SQL command Posted by joe Degorio @ 12/28/2008 8:06:53 AM
RE: help with SQL command Posted by mike castillo @ 12/28/2008 8:35:35 AM
RE: help with SQL command Posted by joe Degorio @ 12/28/2008 12:42:08 PM
RE: help with SQL command Posted by mike castillo @ 12/29/2008 2:22:41 AM
RE: help with SQL command Posted by tushar @ 12/28/2008 10:13:42 AM
RE: help with SQL command Posted by joe Degorio @ 12/28/2008 12:44:53 PM
RE: help with SQL command Posted by Anders Altberg @ 12/28/2008 8:39:35 PM
RE: help with SQL command Posted by joe Degorio @ 12/29/2008 10:50:19 AM
RE: help with SQL command Posted by Jun Tangunan @ 12/29/2008 5:25:47 AM