|
| Can't we delete records from multiple table in a single query. |
 |
Wed, 25 Jul 2007 05:13:50 EDT |
Can we delete records from multiple tables in a single query ?
This can be done with referencial integrity, using CASCADE of dependent and
parent table, But other than this how we can do the same.
C:\Program Files\IBM\SQLLIB\BIN>DB2 DELETE FROM TBL_FORM_HEADER,
TBL_FORM_DETAILS WHERE TBL_FORM_HEADER.FORM_ID = TBL_FORM_DETAILS.FORM_ID AND
TABLE_FORM_HEADER.FORM_ID = 107
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0104N An unexpected token "," was found following "FROM
TBL_FORM_HEADER".
|
| Post Reply
|
| Re: Can't we delete records from multiple table in a single query. |
 |
Sat, 04 Aug 2007 10:56:59 +020 |
Dear,
this is not an valid command line for DB2.
Put your sql query in a file like "query.sql" and call the db2 program
with arguments like:
db2 -tvf query.sql
Regards,
Andreas.
sandip.bhoi@gmail.com wrote:
> Can we delete records from multiple tables in a single query ?
>
> This can be done with referencial integrity, using CASCADE of dependent and
parent table, But other than this how we can do the same.
>
>
> C:\Program Files\IBM\SQLLIB\BIN>DB2 DELETE FROM TBL_FORM_HEADER,
TBL_FORM_DETAILS WHERE TBL_FORM_HEADER.FORM_ID = TBL_FORM_DETAILS.FORM_ID AND
TABLE_FORM_HEADER.FORM_ID = 107
> DB21034E The command was processed as an SQL statement because it was not
a
> valid Command Line Processor command. During SQL processing it returned:
> SQL0104N An unexpected token "," was found following "FROM
TBL_FORM_HEADER".
|
| Post Reply
|
| Re: Can't we delete records from multiple table in a single query. |
 |
Sun, 05 Aug 2007 06:57:23 -040 |
Referential constraints is the way to go in a case like this.
sandip.bhoi@gmail.com wrote:
> Can we delete records from multiple tables in a single query ?
>
> This can be done with referencial integrity, using CASCADE of dependent and
parent table, But other than this how we can do the same.
>
>
> C:\Program Files\IBM\SQLLIB\BIN>DB2 DELETE FROM TBL_FORM_HEADER,
TBL_FORM_DETAILS WHERE TBL_FORM_HEADER.FORM_ID = TBL_FORM_DETAILS.FORM_ID AND
TABLE_FORM_HEADER.FORM_ID = 107
> DB21034E The command was processed as an SQL statement because it was not
a
> valid Command Line Processor command. During SQL processing it returned:
> SQL0104N An unexpected token "," was found following "FROM
TBL_FORM_HEADER".
|
| Post Reply
|
| Re: Can't we delete records from multiple table in a single query. |
 |
Sun, 05 Aug 2007 20:30:54 +020 |
sandip.bhoi@gmail.com wrote:
> Can we delete records from multiple tables in a single query ?
>
> This can be done with referencial integrity, using CASCADE of dependent
> and parent table, But other than this how we can do the same.
>
>
> C:\Program Files\IBM\SQLLIB\BIN>DB2 DELETE FROM TBL_FORM_HEADER,
> TBL_FORM_DETAILS WHERE TBL_FORM_HEADER.FORM_ID = TBL_FORM_DETAILS.FORM_ID
> AND TABLE_FORM_HEADER.FORM_ID = 107
This is not SQL (and I don't mean the specific DB2 dialect you may be
using). Maybe you could tell us exactly the semantics that such a
statement (if it were legal) should have...
I'm not sure if this is allowed by DB2 UDB, but you could try this:
SELECT COUNT(*)
FROM OLD TABLE ( DELETE FROM table1 WHERE ... )
UNION ALL
SELECT COUNT(*)
FROM OLD TABLE ( DELETE FROM table2 WHERE ... )
--
Knut Stolze
DB2 z/OS Utilities Development
|
| Post Reply
|
|
|
|
|
|
|
|
|
|