469,270 Members | 1,097 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,270 developers. It's quick & easy.

delete with join - is it possible ??

Hi!!

Is it possible to delete (or update) with join ??
For example

DELETE
a
FROM
TableA a
INNER JOIN TableB b ON b.id = a.bId
AND b.name = 'someName'

In MSSQL it works fine, but not in db2 :-(
Should I use IN or EXISTS ?? - What is faster and what about IN limitations
??

Please let me know if i can use joins in delete or update...
My DB2 version is 8.1.7
--

Pozdro, Wojtas
http://www.e-krug.com/
Jun 13 '07 #1
4 37625
news.onet.pl wrote:
Hi!!

Is it possible to delete (or update) with join ??
For example

DELETE
a
FROM
TableA a
INNER JOIN TableB b ON b.id = a.bId
AND b.name = 'someName'

In MSSQL it works fine, but not in db2 :-(
Should I use IN or EXISTS ?? - What is faster and what about IN limitations
??

Please let me know if i can use joins in delete or update...
My DB2 version is 8.1.7

Try

Delete from tablea where bid in
(select id from tableb where name='somename')
Jun 13 '07 #2
Try
>
Delete from tablea where bid in
(select id from tableb where name='somename')
I know i can do this this way, but what about IN limitations??
And i think it is slower than delete with join...

Regards, Wojtas
Jun 13 '07 #3
news.onet.pl wrote:
>Try

Delete from tablea where bid in
(select id from tableb where name='somename')

I know i can do this this way, but what about IN limitations??
And i think it is slower than delete with join...
Not sure what you mean by slower as well as the limitations.
If you don't like IN, use EXISTS:
DELETE FROM T WHERE EXISTS(SELECT 1 FROM S WHERE T.pk = S.pk)

You will find that both IN and EXIST are rewritten by the optimizer as JOIN.

You can also use MERGE:
MERGE INTO T USING S ON S.pk = T.pk
WHEN MATCHED THEN DELETE

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Jun 13 '07 #4
>In MSSQL it works fine, but not in db2 :-( <<

If you think about that proprietary syntax, it makes no sense. An
alias is supposed to act as if a new working table is created in the
statement. Thus, you would be updating "a" and never touching TableA
at all! Even worse, the derived table is supposed to be materialized
and this makes lots of problems.

The worse thing is the UPDATE.. FROM.. syntax in SQL Server which will
return the wrong answers. If you have been using this vendor
extension, then you need to review ALL your code and rewrite it to
Standard SQL, which is what DB2 supports.
>Should I use IN or EXISTS ?? - What is faster and what about IN limitations <<
EXISTS() is usually preferred because of problems with NULLs when you
use [NOT] IN () predicates. But you can trust the optimizer to do a
good job in either case.

Jun 14 '07 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

6 posts views Thread by Joel Goldstick | last post: by
2 posts views Thread by Vector | last post: by
14 posts views Thread by php newbie | last post: by
2 posts views Thread by Rotsj | last post: by
3 posts views Thread by Ian Boyd | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.