Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: Andy Kramek
  Where is Andy Kramek?
 Hot Springs Village
 Arkansas - United States
 Andy Kramek
 To: Samir Ibrahim
  Where is Samir Ibrahim?
 Chekka
 Lebanon
 Samir Ibrahim
 Tags
Subject: RE: UPDATE SQL Start from Second match
Thread ID: 209792 Message ID: 209801 # Views: 2 # Ratings: 1
Version: Visual FoxPro 9 SP2 Category: Databases, Tables and SQL Server
Date: Wednesday, December 31, 2008 11:42:18 AM         
   


Hi Samir

> Suppose my table is like this
>

> p_id p_amount p_status
> 1 10 .F.
> 1 36 .F.
> 2 14 .F.
> 2 58 .F.
> 2 45 .F.
>

>
>
nID = 1
> nDisc = 1.5
> SELECT * FROM mytable where p_id = nID && 2 Records will be selected
> UPDATE mytable SET p_amount = p_amount - nDisc WHERE p_id = nID and not p_status and RECNO() <> 1
> 

>
> I don't want to use RECNO()
>
> I want to update all the records except record number 1
>
> I care for the SQL statement, but if that helps, it is on VB6 + MySQL + ADO

Happy New Year!

I thihnk you need some additional criteria in the table to do this reliably. What you are looking for is called an "Exclusion Query" because it acts on only certain records. One possible solution would be to use a date and flag the date of creation for each record. Then you can exclude all records having their 'createdate' the same as the earliest create date for each ID.

Something like this should work in MYSql - this will work in VFP (Version 9+) :

CREATE CURSOR MyTable (p_id I,  p_amount Y,  p_status L, createdate D)

INSERT INTO MyTable VALUES ( 1, 10, .F., CTOD('01/01/2008') )
INSERT INTO MyTable VALUES ( 1, 36, .F., CTOD('03/03/2008') )
INSERT INTO MyTable VALUES ( 2, 14, .F., CTOD('02/02/2008') )
INSERT INTO MyTable VALUES ( 2, 58, .F., CTOD('04/04/2008') )
INSERT INTO MyTable VALUES ( 2, 45, .F., CTOD('05/05/2008') )

SELECT TGT.p_id, TGT.p_amount, TGT.p_status, TGT.createdate ;
  FROM MyTable TGT ;
       LEFT OUTER JOIN ( SELECT p_id, MIN( createdate ) created FROM MyTable GROUP BY p_id ) EXC ;
            ON EXC.p_id = TGT.p_id ;
 WHERE tgt.createdate > EXC.created ;
 INTO CURSOR result
 
 BROWSE LAST NOCAPTION

This query returns

P_id P_amount P_status Createdate
1 36.0000 .F. 03/03/2008
2 58.0000 .F. 04/04/2008
2 45.0000 .F. 05/05/2008

which, as you can see is everything but the first occurrence of each value for p_id.

Regards
Andy Kramek
Microsoft MVP (Visual FoxPro)
Tightline Computers Inc, Akron Ohio, USA

ENTIRE THREAD

UPDATE SQL Start from Second match Posted by Samir Ibrahim @ 12/31/2008 10:54:20 AM
RE: UPDATE SQL Start from Second match Posted by Stefan Wuebbe @ 12/31/2008 11:25:26 AM
RE: UPDATE SQL Start from Second match Posted by Samir Ibrahim @ 12/31/2008 12:36:14 PM
RE: UPDATE SQL Start from Second match Posted by Stefan Wuebbe @ 12/31/2008 12:44:27 PM
RE: UPDATE SQL Start from Second match Posted by Andy Kramek @ 12/31/2008 11:42:18 AM
RE: UPDATE SQL Start from Second match Posted by Stefan Wuebbe @ 12/31/2008 11:54:02 AM
RE: UPDATE SQL Start from Second match Posted by Andy Kramek @ 12/31/2008 11:59:20 AM
RE: UPDATE SQL Start from Second match Posted by Stefan Wuebbe @ 12/31/2008 12:08:57 PM
RE: UPDATE SQL Start from Second match Posted by Samir Ibrahim @ 12/31/2008 12:39:59 PM
RE: UPDATE SQL Start from Second match Posted by Andy Kramek @ 12/31/2008 4:39:59 PM
RE: UPDATE SQL Start from Second match Posted by Anders Altberg @ 1/1/2009 5:39:45 PM
RE: UPDATE SQL Start from Second match Posted by Andy Kramek @ 1/1/2009 7:42:36 PM
RE: UPDATE SQL Start from Second match Posted by Anders Altberg @ 1/2/2009 11:30:30 AM
RE: UPDATE SQL Start from Second match Posted by Samir Ibrahim @ 1/2/2009 10:15:20 AM
RE: UPDATE SQL Start from Second match Posted by Anders Altberg @ 12/31/2008 12:17:03 PM
RE: UPDATE SQL Start from Second match Posted by Samir Ibrahim @ 12/31/2008 12:30:08 PM