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]

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" <rm*******@gmail.comwrote in message news:d2**********************************@m45g2000 hsb.googlegroups.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]

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]

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]

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]

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