May 29, 2008 at 8:00 am
Hi,
If I run the following T-SQL query, I get 2 rows displayed in the 'Results' pane:
SELECT * FROM
KP.ADD K LEFT JOIN RP.ADD R ON
K.ADX_TYPE = R.ADX_TYPE AND
K.ADX_COMPANY = R.ADX_COMPANY AND
K.ADX_CODE = R.ADX_CODE
WHERE (((R.ADX_TYPE) Is Null) AND
((R.ADX_COMPANY) Is Null) AND
((R.ADX_CODE) Is Null))
ORDER BY K.ADX_TYPE,
K.ADX_COMPANY,
K.ADX_CODE;
I need to delete the 2 rows (from KP table). The Type, Company and Code fields are the Primary key in both the KP and RP tables.
Can the delete be coded in a similar query? Any help please.
Thanks in advance,
Neal
May 29, 2008 at 8:07 am
hi,
Use something like this.
DELETE
FROM T1
WHERE ID IN
(SELECT [1].ID
FROM T1 [1] INNER JOIN T2 [2] ON [2].ID = [1].ID)
Your Select would be in the Where clause and should return something unique to identity the row in T1!
I hope this helps
Thanks
Chris
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
May 29, 2008 at 8:10 am
A Christopher has mentioned, wrap your query inside another as a sub-select and return only the PKs needed to the outer query.
May 29, 2008 at 9:43 am
I've always used the 'where exists ()' or 'where not exists ()' clause - sometimes it seems to run a bit faster than using a 'where x in (y)' - I'm not entirely sure why but I think its down to short circuiting where the first record retrieved proves the conditional clause to be either true or false. I think the 'where x in (y)' clause actually fully runs the subquery to get all results before deciding whether x is in (y)...
DELETE FROM
KP.ADD K
WHERE EXISTS (SELECT * FROM RP.ADD R WHERE K.ADX_TYPE = R.ADX_TYPE
AND K.ADX_COMPANY = R.ADX_COMPANY
AND K.ADX_CODE = R.ADX_CODE )
May 30, 2008 at 3:10 am
Thanks guys.
Firstly, Tony I tried your example with a SELECT statement to begin (and NOT EXISTS), which is fine (displays 2 rows - as expected)
SELECT * FROM
KP.ADD K
WHERE NOT EXISTS (SELECT * FROM RP.ADD R WHERE K.ADX_TYPE = R.ADX_TYPE
AND K.ADX_COMPANY = R.ADX_COMPANY
AND K.ADX_CODE = R.ADX_CODE)
But if I use the DELETE statement I found that I have to remove the 'K' pseudonym, but 0 rows are affected.
****************************************************
Secondly, Christopher/Steve, I'm unsure about the correct syntax where there is more than one 'key' field. I've tried several variations but I'm not getting anywhere with this.
Sorry, but can anyone help further?
May 30, 2008 at 3:54 am
Hi,
I've just tried the delete on our sandpit machine and your absolutely right that you can't use an alias on the table where the data is being deleted but the query does work - I'm guessing that the problem was that the alias was removed from the subquery and the subquery had trouble working our where the data was coming from as both tables have columns of the same name. Try fully qualifying the columns like so:
DELETE FROM
KP.ADD
WHERE NOT EXISTS (SELECT * FROM RP.ADD R WHERE [DatabaseNameGoesHere].KP.ADD.ADX_TYPE = R.ADX_TYPE
AND [DatabaseNameGoesHere].KP.ADD.ADX_COMPANY = R.ADX_COMPANY
AND [DatabaseNameGoesHere].KP.ADD.ADX_CODE = R.ADX_CODE)
May 30, 2008 at 4:09 am
It is strange that the query requires a database name, but it works!
Many thanks to all that helped me.
Tony, you deserve a star.
May 30, 2008 at 4:52 am
I prefer to use the same syntax as for a select when deleting, instead of twisting it to WHERE EXISTS... I think this is what you were asking about originally - and yes, it can be done. You just have to follow some rules when doing it:
If there are several tables, you need to mention the table from which you are deleting. If that table uses an alias, you must use that alias in DELETE clause.
DELETE K
-- select K.*
FROM KP.ADD K
LEFT JOIN RP.ADD R ON K.ADX_TYPE = R.ADX_TYPE
ANDK.ADX_COMPANY = R.ADX_COMPANY
AND K.ADX_CODE = R.ADX_CODE
WHERE R.ADX_TYPE Is Null
AND R.ADX_COMPANY Is Null
AND R.ADX_CODE Is Null
If the select works, the delete will work too. Is KP and RP owner (schema) of the table?
May 30, 2008 at 5:13 am
I don't know if this is an issue or not, but the example select is incorrectly written.
It specifies a LEFT JOIN, but since all the R.column filters are placed in the WHERE clause,
this is in effect an INNER JOIN instead.
Dunno if it matters in this case, but the op might want to check it out anyway 🙂
/Kenneth
May 30, 2008 at 5:40 am
Actually, Kenneth, it isn't INNER JOIN - all conditions in WHERE clause are IS NULL 😎 that means "corresponding row does not exist". It is a bit confusing because of the multicolumn key, but it should work correctly as far as I can understand the query.
The problem you describe would occur if WHERE clause would contain some values (like R.ADX_CODE = 20).
June 9, 2008 at 4:47 am
Ah, you're absolutely correct.
That's what you get when you don't read close enough.. 😉
/Kenneth
June 9, 2008 at 6:54 am
I beleive you can also write the DELETE this way:
DELETE FROM KP
FROM
KP.[ADD] K LEFT JOIN RP.[ADD] R ON
K.ADX_TYPE = R.ADX_TYPE AND
K.ADX_COMPANY = R.ADX_COMPANY AND
K.ADX_CODE = R.ADX_CODE
WHERE (((R.ADX_TYPE) Is Null) AND
((R.ADX_COMPANY) Is Null) AND
((R.ADX_CODE) Is Null))
Haven't had a chance to try it on test data, but the syntax checks OK.
Always foudn the FROM ... FROM confusing, but SQL server seems to like it. Don't know how this compares performance wise to the Exists/ In ways.
Cheers,
Rodders...
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply