2 replaced http://dba.stackexchange.com/ with https://dba.stackexchange.com/
source | link

DELETE TECHNIQUE #1

If you have a massive delete of keys, you may want to resort to the opposite of doing a massive DELETE: Create the table with the remaining values.

Let's say you loaded a table from the CSV file called KeysToDelete with the field mykey

Here is how to perform the massive DELETE without using DELETE

CREATE TABLE mynewtable FROM mytable;
INSERT INTO mynewtable
SELECT A.* FROM mytable A
LEFT JOIN KeysToDelete B USING (mykey)
WHERE B.mykey IS NULL;
ALTER TABLE mytable RENAME myoldtable;
ALTER TABLE mynewtable RENAME mytable;

That's it.

DELETE TECHNIQUE #2

If you want to use DELETE and the table is not that big, you could do a DELETE JOIN as follows:

DELETE A.* FROM mytable A INNER JOIN KeysToDelete B USING (mykey);

Here are my earlier posts where I discuss these techniques:

Give it a Try !!!

DELETE TECHNIQUE #1

If you have a massive delete of keys, you may want to resort to the opposite of doing a massive DELETE: Create the table with the remaining values.

Let's say you loaded a table from the CSV file called KeysToDelete with the field mykey

Here is how to perform the massive DELETE without using DELETE

CREATE TABLE mynewtable FROM mytable;
INSERT INTO mynewtable
SELECT A.* FROM mytable A
LEFT JOIN KeysToDelete B USING (mykey)
WHERE B.mykey IS NULL;
ALTER TABLE mytable RENAME myoldtable;
ALTER TABLE mynewtable RENAME mytable;

That's it.

DELETE TECHNIQUE #2

If you want to use DELETE and the table is not that big, you could do a DELETE JOIN as follows:

DELETE A.* FROM mytable A INNER JOIN KeysToDelete B USING (mykey);

Here are my earlier posts where I discuss these techniques:

Give it a Try !!!

DELETE TECHNIQUE #1

If you have a massive delete of keys, you may want to resort to the opposite of doing a massive DELETE: Create the table with the remaining values.

Let's say you loaded a table from the CSV file called KeysToDelete with the field mykey

Here is how to perform the massive DELETE without using DELETE

CREATE TABLE mynewtable FROM mytable;
INSERT INTO mynewtable
SELECT A.* FROM mytable A
LEFT JOIN KeysToDelete B USING (mykey)
WHERE B.mykey IS NULL;
ALTER TABLE mytable RENAME myoldtable;
ALTER TABLE mynewtable RENAME mytable;

That's it.

DELETE TECHNIQUE #2

If you want to use DELETE and the table is not that big, you could do a DELETE JOIN as follows:

DELETE A.* FROM mytable A INNER JOIN KeysToDelete B USING (mykey);

Here are my earlier posts where I discuss these techniques:

Give it a Try !!!

1
source | link

DELETE TECHNIQUE #1

If you have a massive delete of keys, you may want to resort to the opposite of doing a massive DELETE: Create the table with the remaining values.

Let's say you loaded a table from the CSV file called KeysToDelete with the field mykey

Here is how to perform the massive DELETE without using DELETE

CREATE TABLE mynewtable FROM mytable;
INSERT INTO mynewtable
SELECT A.* FROM mytable A
LEFT JOIN KeysToDelete B USING (mykey)
WHERE B.mykey IS NULL;
ALTER TABLE mytable RENAME myoldtable;
ALTER TABLE mynewtable RENAME mytable;

That's it.

DELETE TECHNIQUE #2

If you want to use DELETE and the table is not that big, you could do a DELETE JOIN as follows:

DELETE A.* FROM mytable A INNER JOIN KeysToDelete B USING (mykey);

Here are my earlier posts where I discuss these techniques:

Give it a Try !!!