DB2 equivalent to this Sybase syntax

[Richard]

I'm just getting my feet wet in DB2 UDB (9.x), after years as a Sybase
DBA. In Sybase, if I wanted to delete data from one table based on
joining that table to a second table, I'd use this syntax

delete TABLE_A
from TABLE_A T1,
TABLE_B T2
where T1. col-a = T2.col-a and
T1.col-b = T2.col-b

What's the equivalent syntax in DB2?

Thanks.

[Dan van Ginhoven]

Re: DB2 equivalent to this Sybase syntax

Hi!

Look at the merge statement:

MERGE INTO Table_A A
USING Table_B B
ON A.col1 = B.col1
AND A.col2 = B.col2
WHEN MATCHED THEN
DELETE;

/dg

"Richard" <rmcgorman@gmai l.comwrote in message news:d226d7b0-386c-4caf-aa95-f1d83a260dfb@m4 5g2000hsb.googl egroups.com...

I'm just getting my feet wet in DB2 UDB (9.x), after years as a Sybase
DBA. In Sybase, if I wanted to delete data from one table based on
joining that table to a second table, I'd use this syntax
>
delete TABLE_A
from TABLE_A T1,
TABLE_B T2
where T1. col-a = T2.col-a and
T1.col-b = T2.col-b
>
What's the equivalent syntax in DB2?
>
Thanks.

[Richard]

Re: DB2 equivalent to this Sybase syntax

Thanks, that worked. :-)

I incorrectly guessed that I could use the same logic to delete rows
where
there's NO matching record. Here's what I tried (bad syntax):

merge into Table_A T1
USING Table_B T2
on T1.col_a = T2.col_a
when not matched then delete;

What I'm trying to achieve now is: delete the parent record if there
are no remaining children.

Thanks again.

[Serge Rielau]

Re: DB2 equivalent to this Sybase syntax

Richard wrote:

Thanks, that worked. :-)
>
I incorrectly guessed that I could use the same logic to delete rows
where
there's NO matching record. Here's what I tried (bad syntax):
>
merge into Table_A T1
USING Table_B T2
on T1.col_a = T2.col_a
when not matched then delete;
>
What I'm trying to achieve now is: delete the parent record if there
are no remaining children.
>
Thanks again.
>

DELETE FROM T WHERE NOT EXISTS(SELECT 1 FROM T AS CHILD WHERE T.pk =
child.fk)

That will wipe one level. Now if you want this to cascade things are
getting interesting....

Cheers
Serge


--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[Richard]

Re: DB2 equivalent to this Sybase syntax

Thanks.

I had justed tried the Sybase syntax, which worked. I'm assuming it
equivalent to
what Serge wrote. Here's what I used:

delete from Table_A T1
where not exists (select 1 from Table_B T2 where
T2.col_a = T1.col_a);

[Serge Rielau]

Re: DB2 equivalent to this Sybase syntax

Richard wrote:

Thanks.
>
I had justed tried the Sybase syntax, which worked. I'm assuming it
equivalent to
what Serge wrote. Here's what I used:
>
delete from Table_A T1
where not exists (select 1 from Table_B T2 where
T2.col_a = T1.col_a);

Yes that's ANSI SQL.


--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab