473,854 Members | 1,638 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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 42103
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

6
2454
by: Joel Goldstick | last post by:
What is wrong with this? IT gives this error: #1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT pp.propertyindex, pp.photoindex FROM test AS pp JOIN ren The select portion does what I want. test is a linking table between a rentalproperties and a photos. I want to delete all the photos associated with a certain property
2
6875
by: Vector | last post by:
I have this working fine in Access and MSSQL. While trying to convert to MySQL the result is not what I'm looking for to get.. Two tables: T1 ---------------- ID1|cName | ---------------- 10 |Electronics|
14
3621
by: php newbie | last post by:
I am getting error messages when I try to delete from a table using the values in the table itself. The intent is to delete all rows from TableA where col_2 matches any of the col_1 values. DELETE FROM TableA FROM TableA x INNER JOIN TableA y ON (x.col_1 = y.col_2) Error msg: The table 'TableA' is ambiguous. Can this be done with SQL or should I use T-SQL with cursors here?
2
20482
by: Rotsj | last post by:
Hi, if i run the following query: select * from std_order_lines sol where exists(select * from std_orders so, customers c where so.customers_id = c.customers_id and c.test_customer = 'Y' and so.order_id = sol.order_id)
3
23104
by: Ian Boyd | last post by:
i know nothing about DB2, but i'm sure this must be possible. i'm trying to get a client to create a view (which it turns out is called a "Logical" in DB2). The query needs a LEFT OUTER JOIN, but he doesn't know how to do that, or even if he can, and i don't have to time to learn DB2 from scratch right now. The following SQL Query is a trimmed sample of the full View (i.e. Logical) definition - and i would create it on an SQL based...
3
7564
by: deko | last post by:
From what I understand, an Inner Join narrow the rows selected to the table with the least results... and an Outer Join does the opposite... SELECT qryTxToQ1.Q1Total, qryTxToQ2.Q2Total, qryTxToQ3.Q3Total, qryTxToQ4.Q4Total FROM qryTxToQ4 OUTER JOIN (qryTxToQ3 OUTER JOIN (qryTxToQ1 OUTER JOIN qryTxToQ2 ON qryTxToQ1.TxAcct_ID = qryTxToQ2.TxAcct_ID) ON qryTxToQ3.TxAcct_ID = qryTxToQ2.TxAcct_ID) ON qryTxToQ4.TxAcct_ID = qryTxToQ3.TxAcct_ID;
3
1722
by: dadapeer | last post by:
Hai Every body ......... iam want one clarification.that folder can be delete automatically by setting time and date period to delete.iff possible give me u r answer thanks..........
3
9928
code green
by: code green | last post by:
I have a parent table `products` that contains all company products. The product details are divided amongst 4-5 product details child tables such as `paper`, `pens`, `books`, `sets`, `pens_paper`. The 'product_ids` may be in one or more of these child tables but always in `products`. Is a single DELETE query possible that will delete all occurences of a `product_id` in the tables where the `product_id` occurs.
0
11031
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10685
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10763
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10371
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9518
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
5750
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5942
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
4162
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3188
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.