Board index » delphi » how to change some records with another records at the same table

xiuqin

Delphi Developer
Sat, 13 Nov 2004 17:23:49 GMT

how to change some records with another records at the same table

hi,every
I have a table below:

in_no           zyh                                   paidcount
200205240030 P0024000075                              8
200205240030 P0030000104                              5
200205240030 P0032000005                              4
200205240030 P0034000010                              3
200205240030 P0124000053                              2
200205240031 P0023000073                              1
200205240031 P0024000075                              1
200205240031 P0030000104                              1
200205240031 P0032000005                              1
200205240031 P0034000010                              1
200205240031 P0124000053                              1

 update  a set paidcount=a.paidcount-b.paidcount   from (select * from
indetail where in_no='200205240030') as a,(select * from indetail where
in_no='200205240031') as b
         where a.zyh=b.zyh
the query dont work, maybe some one can tell me how to achieve the correct
result.

thanks in advance.

 
Dell Stinnet

Delphi Developer
Mon, 15 Nov 2004 02:02:46 GMT

Re:how to change some records with another records at the same table


I'm not certain it will work, but you might try something like this:

Update indetail
Set PaidCount = (Select (a.PaidCount - b.PaidCount)
                           from indetail a,
                                   indetail b
                           where a.in_no='200205240030'
                              and  b.in_no='200205240031'
                              and  a.zyh = b.zyh)

xiuqi

Delphi Developer
Sat, 13 Nov 2004 17:14:05 GMT

Re:how to change some records with another records at the same table


hi,every
I have a table below:

in_no           zyh                                   paidcount
200205240030 P0024000075                              8
200205240030 P0030000104                              5
200205240030 P0032000005                              4
200205240030 P0034000010                              3
200205240030 P0124000053                              2
200205240031 P0023000073                              1
200205240031 P0024000075                              1
200205240031 P0030000104                              1
200205240031 P0032000005                              1
200205240031 P0034000010                              1
200205240031 P0124000053                              1

 update  a set paidcount=a.paidcount-b.paidcount   from (select * from
indetail where in_no='200205240030') as a,(select * from indetail where
in_no='200205240031') as b
         where a.zyh=b.zyh
the query dont work, maybe some one can tell me how to achieve the correct
result.

thanks in advance.

Other Threads

1. Changing Key Fields in Master Record without Orphaning Detail Records

2. Couldn't perform the edit record because another user changed the record

3. Number of Records doesn't change after posting a new record

4. Detecting a Lock record (not a lock table but the record)

5. problems moving to last record in a 30000 record table wih mssql 7.0

6. duplicate a record to an other record in the same table

7. Accessing table records using record structures

8. Retriving n records from n1 record to n2 record

9. Record Cloning or Insert New Record using data from currently selected record

10. Q: Record Pointer when Changing a Tables Edit Mode