Deleting millions of rows in MySQL
DELETE FROM `table` WHERE (whatever criteria) ORDER BY `id` LIMIT 1000 Wash, rinse, repeat until zero rows affected. Maybe in a script that sleeps for a second or three between iterations.
sql-delete
delete rows from multiple tables
Well, if you had used InnoDB tables, you could set up a cascading delete with foreign keys that would do it all automatically. But if you have some reason for using MyISAM, You just use a multiple-table DELETE: DELETE FROM boards, topics, messages USING boards INNER JOIN topics INNER JOIN messages WHERE boards.boardid = $boardid … Read more
Deleting a row based on the max value
DELETE FROM table ORDER BY jobPositonId DESC LIMIT 1
Delete all rows with timestamp older than x days
DELETE FROM on_search WHERE search_date < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 180 DAY))
MySQL LIMIT on DELETE statement
simply use DELETE FROM test WHERE 1= 1 LIMIT 10
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
How to delete in MS Access when using JOIN’s?
Delete Table1.* From Table1 Where Exists( Select 1 From Table2 Where Table2.Name = Table1.Name ) = True To expand on my answer, the official SQL specification does not provide for using Joins in action queries specifically because it can create ambiguous results. Thus, it is better (and Access is much happier) if you can avoid … Read more
I got error “The DELETE statement conflicted with the REFERENCE constraint”
The error means that you have data in other tables that references the data you are trying to delete. You would need to either drop and recreate the constraints or delete the data that the Foreign Key references. Suppose you have the following tables dbo.Students ( StudentId StudentName StudentTypeId ) dbo.StudentTypes ( StudentTypeId StudentType ) … Read more
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 … Read more