February 18, 2008 at 11:32 am
Hi,
I need to read through all records in my Customer table.
If the Status field in any row <> 'checked', then I need to delete ALL records.
Can anyone help please?
Thanks in advance,
Neal
February 18, 2008 at 12:20 pm
Do you mean you need to delete all records where the value = 'Checked' or if any value = 'Checked' you're going to delete all values?
If the former, simply make "WHERE value='Checked'" part of the delete query. If the latter, use
IF EXISTS (SELECT 1 FROM Table WHERE value = 'Checked')
BEGIN
DELETE Table
END
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 18, 2008 at 12:26 pm
Sorry, I mean the latter.
If any row where Status = 'Checked' then delete ALL records, irresspective of the values.
So if the Customer table has 100 rows and only 1 row has a Status value of 'Checked' then delete 100 records
February 18, 2008 at 1:03 pm
Something like this would work:
DELETE
FROM MYTABLE
WHERE MYKEY IN (
SELECT MYKEY
FROM MYTABLE
WHERE STATUS = 'CHECKED'
)
February 18, 2008 at 1:12 pm
Actually that's only going to delete individual rows. The EXISTS followed by a full DELETE with no WHERE clause is probably what he's looking for.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 18, 2008 at 1:19 pm
Gotcha. I misunderstood the requirement. I thought we were deleting all rows of a particular id, where at least on row had a status of "Checked".
Yeah, we would need a delete from table (no where) or a truncate table.
February 18, 2008 at 1:20 pm
Oh and thanks for the correction Grant. 😀
February 18, 2008 at 1:23 pm
If no Status values = 'Checked',
then I don't want to delete any records.
February 18, 2008 at 1:26 pm
Grants first solution will do that. will it not?
-- Cory
February 18, 2008 at 1:27 pm
If you use the IF EXISTS clause, it will only fire the code within the BEGIN...END when it finds the existance of a row in the SELECT statement. I'm pretty sure that's what you're looking for based on what you described.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 19, 2008 at 2:15 am
It was successful.
Many thanks
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply