Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: Stefan Wuebbe
  Where is Stefan Wuebbe?
 Hamburg
 Germany
 Stefan Wuebbe
 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: 209798 # Views: 2 # Ratings: 0
Version: Visual FoxPro 9 SP2 Category: Databases, Tables and SQL Server
Date: Wednesday, December 31, 2008 11:25:26 AM         
   


> Hi all,
>
> 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
>
Hi Sam,

You can have a sub-query in Where. How is the order defined in your table? Since SQL has no concept of a "Natural" order like xBase RecNo(), I assumed you want p_amount as order-by column.
Otherwise you'd probably need a timestamp column (and a primary-key column), I think.

CREATE CURSOR temp (p_id I,  p_amount I, p_status L)
INSERT INTO temp VALUES (1,      10,        .F.  )
INSERT INTO temp VALUES (1,      36,        .F.  )
INSERT INTO temp VALUES (2,      14,        .F.  )
INSERT INTO temp VALUES (2,      58,        .F.  )
INSERT INTO temp VALUES (2,      45,        .F.  )
nID = 1
nDisc = 1.5
UPDATE temp SET p_amount = p_amount - nDisc WHERE p_id = nID and not p_status and p_amount > (Select MIN(p_amount) From temp)
BROWSE



hth
-Stefan

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