Re: Delete in SQL

you need to specify a field and then select that field in your subselect.

DELETE FROM Myfile WHERE myField IN (SELECT myField FROM MyOtherFile WHERE
this = 'that' AND here = 'there')

Get the idea?

Thanks,

Mark

Mark D. Walter
Senior Programmer/Analyst
CCX, Inc.
mwalter@xxxxxxxxxx
http://www.ccxinc.com


                                                                           
             "Bruce Barrett"                                               
             <bruce.barrett@pm                                             
             igroup.com>                                                To 
             Sent by:                  "Midrange Systems Technical         
             midrange-l-bounce         Discussion"                         
             s+mwalter=hanover         <midrange-l@xxxxxxxxxxxx>           
             wire.com@midrange                                          cc 
             .com                                                          
                                                                   Subject 
                                       Delete in SQL                       
             06/29/2005 04:51                                              
             PM                                                            
                                                                           
                                                                           
             Please respond to                                             
             Midrange Systems                                              
                 Technical                                                 
                Discussion                                                 
             <midrange-l@midra                                             
                 nge.com>                                                  
                                                                           
                                                                           




Do you know how to write a delete statement to delete by a composite
key/

I can do it by one, but 3 doesn't seem to work

This is the last one I tried
                            Specify DELETE Statement



 Type choices, press Enter.



   File . . . . . . . . . . .   ISPFDWJS                 Name, F4 for
list
     Library  . . . . . . . .     waide                  Name, F4 for
list
   Correlation  . . . . . . .                            Name



 Type WHERE conditions, press Enter.

   in (select * from waide/dq32pf2 where ctidwj=ctidwja and
ctckwj=ctckwja
   and sccswj=sscswja)




Bottom
 Type choices, press Enter.



   WITH isolation level . . .   1                1=Current level, 2=NC
(NONE)
                                                 3=UR (CHG), 4=CS, 5=RS
(ALL)
                                                 6=RR

              F10=Copy line   F12=Cancel   F14=Delete line   F
Token WAIDE was not valid. Valid tokens: ) ,.


This is what we normally run----(but I need the sccswj field to make it
unique)

DELETE FROM ISDTAPRD/ISPFDA3 WHERE bfnba3 in  (SELECT * FROM
 waide/dq138SMC)


Bruce Barrett
Systems Consultant
PMI Mortgage Insurance Co.
925-658-6152
bruce.barrett@xxxxxxxxxxxx

This e-mail is confidential and may be privileged. If you are not the
intended recipient, please destroy this message and notify the sender.


--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.