The code you tried is in fact two statements. A DELETE followed by a SELECT. You don’t define TOP as ordered by what. For a specific ordering criteria deleting from a CTE or similar table expression is the most efficient way. ;WITH CTE AS ( SELECT TOP 1000 * FROM [mytab] ORDER BY a1 ) … Read more
To delete all records from a table without deleting the table. DELETE FROM table_name use with care, there is no undo! To remove a table DROP TABLE table_name
You simply need to specify on which tables to apply the DELETE. Delete only the deadline rows: DELETE `deadline` FROM `deadline` LEFT JOIN `job` …. Delete the deadline and job rows: DELETE `deadline`, `job` FROM `deadline` LEFT JOIN `job` …. Delete only the job rows: DELETE `job` FROM `deadline` LEFT JOIN `job` ….
A really easy way to do this is to add a UNIQUE index on the 3 columns. When you write the ALTER statement, include the IGNORE keyword. Like so: ALTER IGNORE TABLE jobs ADD UNIQUE INDEX idx_name (site_id, title, company); This will drop all the duplicate rows. As an added benefit, future INSERTs that are … Read more
I like CTEs and ROW_NUMBER as the two combined allow us to see which rows are deleted (or updated), therefore just change the DELETE FROM CTE… to SELECT * FROM CTE: WITH CTE AS( SELECT [col1], [col2], [col3], [col4], [col5], [col6], [col7], RN = ROW_NUMBER()OVER(PARTITION BY col1 ORDER BY col1) FROM dbo.Table1 ) DELETE FROM … Read more
Update: This answer covers the general error classification. For a more specific answer about how to best handle the OP’s exact query, please see other answers to this question In MySQL, you can’t modify the same table which you use in the SELECT part. This behaviour is documented at: http://dev.mysql.com/doc/refman/5.6/en/update.html Maybe you can just join … Read more
You need to specify what table you are deleting from. Here is a version with an alias: DELETE w FROM WorkRecord2 w INNER JOIN Employee e ON EmployeeRun=EmployeeNo WHERE Company = ‘1’ AND Date=”2013-05-06″