Re: Simple Delete Gets Complex..any Ideas
--0__=jYdfCXG3w2QKtRPbDozHjde8N1n7Kf9AZrQkF3KxtMwFKlR8X62uynek
Content-type: text/plain; charset=us-ascii
I think you're looking for a correlated subquery:
Delete from Table_A TA
where EXISTS
(select 'X' from Table_B TB
where TA.PK = TB.FK
)
You can speed this up if Table_A is large, but the number of rows you need
to delete is small by uncorrelating it as follows:
Delete from Table_A TA
where PK IN
(
select FK from Table_B TB
)
This means that Informix will only do the subquery once, instead of for
every row in Table_A. However, if the key is a compound key, you would
need to use concatenation because Informix does not support lists (Boo,
hiss, *&$(#):
Delete from Table_A TA
where PK!||PK2 IN
(
select FK1||FK2 from Table_B TB
)
(Embedded
image moved tracyn @ asd.banctec.com
to file: 07/22/97 01:50 PM
PIC25114.PCX)
To: msamson @ azstarnet.com
cc: informix-list @ rmy.emory.edu (bcc: Greg Salmon)
Subject: Re: Simple Delete Gets Complex..any Ideas
One thing that's been overlooked in these discussions is the fact that
you need TWO sets of parens in order to get this statement to work
properly. I've found that on updates and deletes, one needs one
set for the subquery and one set for the IN clause. To use the
delete statement below as an example:
delete from Table_A
where Table_A.PK in ((Select Table_B.FK from Table_B where {your query
condition}))The results differ if you don't have both sets of parens. I hope
this helps!
Tracy
}
} John,
} You didn't say what the target database is. However you can try this
} on whatever the database you have. It may or may not work depending
} on the target.
}
} delete from Table_A
} where Table_A.PK in (Select Table_B.FK from Table_B where {your query
} condition})
}
} John C. Pugh wrote:
} >
} > Objective:
} >
} > Deleting rows from table "Table_A" based on results of a subquery which
} > requires an additional table "Table_B".
} >
} > current syntax:
} >
} > Delete from Table_A where
} >
} > (select * from Table_A, Table_B where
} > Table_A.PK = Table_B.FK (+))
} >
} > needless to say this doesn't work, I also attempted the following
} >
} > Delete from Table_A where
} > EXISTS
} > (select * from Table_A, Table_B where
} > Table_A.PK = Table_B.FK (+))
} >
} > If my subquery returned one row, Table_A was history (LoL..thank God
} > for
} > RollBacks)!!
} >
} > I also tried:
} >
} > Delete from
} > (select * from Table_A, Table_B where
} > Table_A.PK = Table_B.FK (+))
} >
} > But this attempted fails to "SPECIFY" exactly which table to apply the
} > delete method to.
} >
} > I know in Sybase you would do something like
} >
} > delete Table_A from Table_A, Table_B where Table_A*=Table_B
} >
} > Basically, I'm attempting to delete rows from Table_A based on a
} > criteria
} > that requires the presence of Table_B.
} >
} > Anyone out there have any ideas?????
} >
} > Thanx In Advance...
} >
} > Tony P.
}
} --
} http://www.tucson.com/objectware
} database design, reporting, query, demo tools and freebees
}
--
=========================================================================
= Tracy W. Nedd =
= Banctec Inc. - DBA =
= Silver Spring, Md =
= tracyn@banctec.clark.net or tracyn@asd.banctec.com =
=========================================================================
= ALL RIGHTS RESERVED. =
= This posting is copyright and may be used only for the purposes =
= of professional study, or the professional exchange of information. =
= Use of this posting by any commercial organization for the purposes =
= of vilifying another is explicitly prohibited. =
=========================================================================
--0__=jYdfCXG3w2QKtRPbDozHjde8N1n7Kf9AZrQkF3KxtMwFKlR8X62uynek
Content-type: application/octet-stream;
name="PIC25114.PCX"
Content-transfer-encoding: base64
CgUBCAAAAABoACwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAABaQABAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAD1E9sTzRPHE8MTwhP1E9sTzRPHE8MTwhP1E9sTzRPHE8MTwhP1E9sTzRPH
E8MTwhP1E9sTzRPHE8MTwhP1E9sTzRPHE8MTwhP1E9sTzRPHE8MTwhP1E9sTzRPHE8MTwhP1E9sT
zRPHE8MTwhPwEwzIBgzYE8wTxhPDE8IT7hPOBtcTzBPGE8MTE+wTwgbCBwbCEgbCEgbCEsUG1hPL
E8YTwxMT6hMMwgYHwgLCAwISwgfEEsMCwwbVE8sTxRPDExPpE8MGAwcCBwMCwhLDB8ISwgISwgLD
BtUTyhPFE8MTE+gTwgIHA8ICEw4DDgLDE8USwwLCEMIG1BPKE8UTwxMT5xMCAwcDAg4TDgITwgIS
D8ISD8ISBRICEcICwwbUE8oTxRPCExPmEwYCBwMCDgIOwgLDExITEhPCEg8GxgLDBtMMDAfJE8QT
whMT5hMGwwITBgMCDhLFEw8SE8ISBgIDwhIDEsMGB9MDxwwHxRPDExPlEwYHAhESAg8CwhMPwhMP
xBMPxRIQwgIDAgMCBtMDxwPEDAfDE8IT4RMHwwzCBgLCEhMCDxLIE8MSD8MSwwIQAwIDBgfSDMkD
wgPCDAfCExPbEwfGDMIDDAIHERITEhMSwxMPwxMPwxPDEgIDAgMCwwMCBgzREwfHDMYDDMITE9YT
B8UMyAMGB8ICBhLDAsYTEhMSExIPwhIHAgcCAwUQAgYRBgfSE8UTB8QMwgMMwhMT0hMHxAzLA8IM
BsISDxESExITAw4DxBMSExITwxICBwPCAsMDDMIGB9ITyRMHwwzCExPPEwfDDMkDxQwHwhMGBxIT
AhECEwMOAg7DExITDxMPwxIDAgMCBwMCDAYRBgfSE8kTwhPCDMITE8wTB8MMxwPEDMIHxxMGxBLD
Ag4DDgIGwg/IEgIDwgIDAgwCEMIGB9ITyRMHDAcMwhMTyhMHwgzGA8MMwgfMEwYHwhLCEAIOAg4C
DhDDAhIPxhIFAgXDAgUCEQYH0hPHEwfCDAcPDMITE8gTB8IMxQPDDAfQEwbDEhDEAhAOEA4QwgLG
EgcSBhIGBcMCBcIGB9ATB8UMEwfCDA8HDwwHwhMTxhMHwgzEA8MMB9MTBgfCEhADEMICDhAOEMIC
EQIDxxIGBwbCAgUCEQYHyxMHxAwHwhMHEwzCEwcPBw8MB8MTE8UTBwzEA8IMB9YTBsQSEAMCA8UC
EQIDAgPDEgcSBgfCBgUQAhDCBgfGEwfEDAfGE8INEwzCEw8HwgwHwxPCE8QTBwzDA8IMB9gTBgfE
EhACEMYCEQIDAsQSBhLDBsICEALCBgfCEwfDDAfKEwfCDRMHwhPCDAfEE8ITE8MTBwzCA8IMB9oT
DBIHwxLDDBEDxQIDAgPDEgYSBgfCBgIQAhAGDAfCEwzDE8MHyRMHwhPCBxMHxRPDExPDEwzCAwwH
3RMGxxICEQPDAgMCA8MSBhIGBwYMBhACEAIGDMMTDBPCB8YTwwfHEwfGE8MTwhPDEwwDDAfeEwYH
xxICEQPDAgMCwhIGEgYHBgwGEAIQAsIGB8MTDMYTwwfKEwzGE8MTwhPDE8IMB98TDBLCB8USAgMR
xAISB8ISBgcGDAYQBhAGEAYMB8MMB8kTwwfHEwzGE8MTwhPDEwwPwgzfEwYSB8ISB8ISAhECAwID
EgcSBwYHBgwGEAYQxgzDD8IHxRPDB8kTBwzGE8MTwhPDEwzDD8QM3BPCBhIGwxIGAhECAwIHBgcG
yAzJDxMHzRMHwwwHxxPDE8ITwxMHDMYPxwwH1BMGEgYSBhLLDM4PwwwTDMcTwgfEDAfJE8QTwhMT
xBMHwgzLD9sM0w/GDAfDEwzDEwfEDAfLE8YTwxMTxhMHxAztD8gMBgfIE8QMB84TxxPDE8ITyhMH
xwzbD8sMEAUMBcIMwgYH1RPKE8UTwxMT0RMH2wwGEAYQBhACBQwFDAUMBgwHBgfWE8sTxRPDExPu
EwYMBhAGEAIGDAYMwwYH1xPLE8YTwxMT8BPKBgfYE8wTxhPDExP1E9sTzRPHE8MTwhP1E9sTzRPH
E8MTwhMMAAAAgAAAAIAAgIAAAACAgACAAICAwMDAwNzApsrw//vwoKCkgICA/wAAAP8A//8AAAD/
/wD/AP//////AAAAgAAAAIAAgIAAAACAgACAAICAwMDAwNzApsrw//vwoKCkgICA/wAAAP8A//8A
AAD//wD/AP//////AAAAgAAAAIAAgIAAAACAgACAAICAwMDAwNzApsrw//vwoKCkgICA/wAAAP8A
//8AAAD//wD/AP//////AAAAgAAAAIAAgIAAAACAgACAAICAwMDAwNzApsrw//vwoKCkgICA/wAA
AP8A//8AAAD//wD/AP//////AAAAgAAAAIAAgIAAAACAgACAAICAwMDAwNzApsrw//vwoKCkgICA
/wAAAP8A//8AAAD//wD/AP//////AAAAgAAAAIAAgIAAAACAgACAAICAwMDAwNzApsrw//vwoKCk
gICA/wAAAP8A//8AAAD//wD/AP//////AAAAgAAAAIAAgIAAAACAgACAAICAwMDAwNzApsrw//vw
oKCkgICA/wAAAP8A//8AAAD//wD/AP//////AAAAg