sql-delete
Delete from one table with join
I am not sure about your requirement. What I understood from your question is you want to delete all the emails of jobs which are closed. try this one; DELETE e FROM emailNotification e LEFT JOIN jobs j ON j.jobId = e.jobId WHERE j.active = 1 AND CURDATE() < j.closeDate
Delete all but one duplicate record
ANSI SQL Solution Use group by in a subquery: delete from my_tab where id not in (select min(id) from my_tab group by profile_id, visitor_id); You need some kind of unique identifier(here, I’m using id). MySQL Solution As pointed out by @JamesPoulson, this causes a syntax error in MySQL; the correct solution is (as shown in …
How do I delete all the duplicate records in a MySQL table without temp tables
Add Unique Index on your table: ALTER IGNORE TABLE `TableA` ADD UNIQUE INDEX (`member_id`, `quiz_num`, `question_num`, `answer_num`); Another way to do this would be: Add primary key in your table then you can easily remove duplicates from your table using the following query: DELETE FROM member WHERE id IN (SELECT * FROM (SELECT id FROM …
TSQL Try / Catch within Transaction or vice versa?
Only open a transaction once you are inside the TRY block and just before the actual statement, and commit it straightaway. Do not wait for your control to go to the end of the batch to commit your transactions. If something goes wrong while you are in the TRY block and you have opened a …
How to delete multiple rows with 2 columns as composite primary key in MySQL?
The answer in Oracle is: delete from cpi where (countryid, year) in ((‘AD’, 2010), (‘AF’, 2009), (‘AG’, 1992)) It’s fairly standard SQL syntax and I think MySQL is the same.
How do I delete multiple rows with different IDs?
If you have to select the id: DELETE FROM table WHERE id IN (SELECT id FROM somewhere_else) If you already know them (and they are not in the thousands): DELETE FROM table WHERE id IN (?,?,?,?,?,?,?,?)
Counting the number of deleted rows in a SQL Server stored procedure
Have you tried SET NOCOUNT OFF?
SQL Delete Records within a specific Range [duplicate]
If you use Sql Server delete from Table where id between 79 and 296 Note : the between statement is inclusive, so rows 79 and 296 will also be deleted After your edit : you now clarified that you want : ID (>79 AND < 296) So use this : delete from Table where id …
Delete all rows with timestamp older than x days
DELETE FROM on_search WHERE search_date < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 180 DAY))