Re: DELETE statement with a JOIN

Jeff,

Without knowing which table contains the columns FKTEMPLATELIST and FKLIST, it's hard to say whether the standard equivalent of your proprietary code is:

DELETE
FROM DESCHYPLIST
WHERE FKINFO IN (
  SELECT FKINFO
  FROM TPLDESCLIST
  WHERE FKTEMPLATELIST = @FkTmpl and FKLIST = @PkList);

or

DELETE
FROM DESCHYPLIST
WHERE FKTEMPLATELIST = @FkTmpl AND FKINFO IN (   SELECT FKINFO
  FROM TPLDESCLIST
  WHERE FKLIST = @PkList);

or

DELETE
FROM DESCHYPLIST
WHERE FKLIST = @PkList AND FKINFO IN (
  SELECT FKINFO
  FROM TPLDESCLIST
  WHERE FKTEMPLATELIST = @FkTmpl);

but any of these statements should be easy to port to Oracle.

Hope that helps,
Rich

"Jeff Lambert" <jeffl_at_hypershell.com> wrote in message news:f17e7766.0401161303.d4eddaf_at_posting.google.com...
> Hi. I'm trying to convert this small piece of SQL 2000 code to Oracle
> 8i.
>
> From what I gather in the news groups, you can't delete on a Join. So
> what should I do for this specific case?
>
> DELETE DESCHYPLIST
> FROM DESCHYPLIST DHL JOIN TPLDESCLIST TDL ON (DHL.FKINFO = TDL.FKINFO)
> WHERE FKTEMPLATELIST = @FkTmpl and FKLIST = @PkList
Received on Fri Jan 16 2004 - 15:33:24 CST