delete based on select query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

delete based on select query

Author

Topic

ann
Posting Yak Master

220 Posts

Posted - 2009-05-20 : 10:38:31

I have a query that I can't figure out how to complete - this part works:

select id from TableA
join TableB on TableB.id = TableA.id
where category != 'Fraud'

What I need is to delete all the records in tableA based on the result set of the above query - something like

Delete from comphits where ???
{select id from TableA
join TableB on TableB.id = TableA.id
where category != 'Fraud'}

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2009-05-20 : 10:50:18

There are a few ways to do this, but this will work:


Delete from comphits where id in (
select id from TableA
join TableB on TableB.id = TableA.id
where category != 'Fraud')

assuming you have id in comphits, which I guess you must have.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-05-20 : 11:45:21

Or better yet:


DELETE c
FROM
    comphits c
    JOIN tableA ON a.[Id] = c.[Id]
    JOIN tableB ON b.[Id] = a.[Id]
WHERE
    [a|b].[category] <> 'Fraud'

Best practice:
1) Alias those tables
2) Realise that DELETE / UPDATE syntax is very similar to SELECT -- you don't need to complicate things with WHERE x IN () you can just JOIN
3) fully qualify any column names -- in your code we can't tell which table [category] lives in.
4) I don't like the != syntax <> is more vanilla SQL but it works fine

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

jholovacs
Posting Yak Master

163 Posts

Posted - 2009-05-20 : 12:50:10

quote:
4) I don't like the != syntax <> is more vanilla SQL but it works fine

I'd say that depends on your background. Most modern programming languages (at least the ones I've used) use the != rather than <>, the only exception that I know of being BASIC derivatives.

SELECT TOP 1
w.[name]
FROM
dbo.women w
INNER JOIN
dbo.inlaws i
ON
i.inlaw_id = w.parent_id
WHERE
i.net_worth > 10000000
AND
i.status IN ('dead', 'dying')
AND
w.husband_id IS NULL
ORDER BY
w.hotness_factor DESC

tosscrosby
Aged Yak Warrior

676 Posts

Posted - 2009-05-20 : 13:54:11

quote:
Originally posted by jholovacs

quote:
4) I don't like the != syntax <> is more vanilla SQL but it works fine

I'd say that depends on your background. Most modern programming languages (at least the ones I've used) use the != rather than <>, the only exception that I know of being BASIC derivatives.

I thought I read somewhere that "!" was being deprecated at some point in SQL??????

Terry

-- Procrastinate now!

jholovacs
Posting Yak Master

163 Posts

Posted - 2009-05-20 : 14:39:17

if so, it's news to me. I don't see why they would bother though. If one of the two had to be deprecated, I would think it to be "<>" as the less accepted syntax.

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2009-05-20 : 18:28:38

One question, if you do not do a where on either say delete or update regardless of the join it will udpate and delete all the rows. So now i do a where always.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-05-21 : 04:03:09

The update statement will only UPDATE or DELETE from the table where the join conditions are met. Example code


DECLARE @foo TABLE (
	[ID] INT
	, [value] VARCHAR(50)
	)

DECLARE @bar TABLE (
	[fooId] INT
	, [value] VARCHAR(50)
	)

INSERT @foo ([ID], [value])
      SELECT 1, 'a'
UNION SELECT 2, 'b'
UNION SELECT 3, 'c'
UNION SELECT 4, 'd'
UNION SELECT 5, 'e'

INSERT @bar ([fooId], [value])
      SELECT 1, 'is for apple'
UNION SELECT 2, 'is for bang!'
UNION SELECT 3, 'is for Charlie'

SELECT * FROM @foo

SELECT
	f.[ID]
	, f.[value]
	, b.[value]
FROM
	@foo f
	JOIN @bar b ON b.[fooId] = f.[ID]


-- DELETE
DELETE f
FROM
	@foo f
	JOIN @bar b ON b.[fooId] = f.[Id]

SELECT * FROM @foo

So you don't need a where clause if your joins are enough to select the right rows.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-05-21 : 04:07:57

quote:
Originally posted by jholovacs

quote:
4) I don't like the != syntax <> is more vanilla SQL but it works fine

I'd say that depends on your background. Most modern programming languages (at least the ones I've used) use the != rather than <>, the only exception that I know of being BASIC derivatives.

Totally agree re other languages. However I was only talking about SQL.

I think that in other flavours of SQL the normal syntax is <> (hence why I prefer it -- I'd hope most of my code was not hard to port to another database engine

I too had heard that != is to be deprecated at some future date....

I have nothing against != in other environments -- I actually think it makes more sense than <> (NOT EQUAL TO) is more obvious than (GREATER THAN OR LESS THAN) but for the reasons above I always use <>

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources