2

Stemming from this question, I am going to use that complex sort's results to decide which rows' data to move from one table to another. Though I have not yet tried it, I think there should be no problem to do the "copy" part via a:

INSERT * FROM (...)

Using my results from that SELECT inside the parentheses.

However, if I then user the same query to try to SELECT the same results in order to do a DELETE FROM, the results will no longer include those same rows. So, I need some way to store at least the primary keys (and origin table, since we are talking about 2 origin tables and one destination table) in order to DELETE the correct row from the correct table.

From what I have read and understood, it's not possible to do in a single query, and in any case would require a transaction (which I need to lookup how to correctly do).

So my first approach would be to do the INSERT copy first, and then maybe delete form the two tables any rows containing primary keys appearing in my destination table. It seems a little inefficient since that table would be much larger than the two origin tables, and I figure that from the derived table I got from my subquery, it would be much much more efficient to use the info located there. So, any clues as to how I could best go about this?

  • Could you expand on this with some sample data and your desired result. – Vérace Jul 14 '16 at 3:19
  • @Vérace, you mean copy the sample data from the linked question? – insaner Jul 14 '16 at 4:38
2

Insert everything into a temp table first. Then your insert and delete are simply everything from the temp table rather than your complicated query. This solves your problem of how to do both halves and is probably going to perform better than running your complicated query multiple times.

I do more MS-SQL than My-SQL so I'll link to another answer on Stack Overflow.

https://stackoverflow.com/questions/5859391/create-a-temporary-table-in-a-select-statement-without-a-separate-create-table

On MS-SQL it is simply

  SELECT .... into #MyTempTable FROM ... WHERE..
| improve this answer | |
  • Interesting! My knowledge is still very limited, can you edit to add an example of how to do this? – insaner Jul 12 '16 at 19:05
  • @Evan OP question is tagged with MySQL, not with SQL Server. Beside using temp table with SQL Server will create tempdb IOs. – Julien Vavasseur Jul 12 '16 at 20:32
  • Thanks to this answer though, I quite quickly whipped up a solution using temp tables that solves all my problems.. though not in a single operation, of course. – insaner Jul 12 '16 at 23:21
  • An equivalent for MySQL would probably be CREATE TEMPORARY TABLE tablename AS SELECT .... – Andriy M Jul 15 '16 at 6:48
  • @Evan, I have posted my solution which I came to thanks to your tip. Thanks again! – insaner Jul 29 '16 at 6:53
2

You could use a trigger which insert into table2 what has been removed from table1.

CREATE TABLE table1(id int, name varchar(10), level int);
INSERT INTO table1(id, name, level) VALUES
  (0, 'a', 0)
  , (1, 'b', 1)
  , (2, 'c', 0)
  , (3, 'd', 2)
  , (4, 'e', 1)
  , (4, 'f', 0);

CREATE TABLE table2(id int, name varchar(10), level int);

DELIMITER //

CREATE TRIGGER table1_after_delete
    AFTER DELETE
    ON table1 FOR EACH ROW        
    trig: BEGIN
    IF (@TRIGGER_AFTER_DELETE_ENABLE = FALSE)
    THEN
        LEAVE trig;
    END IF;
    INSERT INTO table2
    ( 
        id
        , name
        , level
    )
    VALUES
    ( 
        OLD.id
        , OLD.name
        , OLD.level 
    );
END; //

DELIMITER ;

DELETE FROM table1 WHERE level = 1;
SET @TRIGGER_AFTER_DELETE_ENABLE = FALSE;
DELETE FROM table1 WHERE level = 2;

This sample deletes everything in table1 where level=1 and then where level=2. It moves it to table2 only when @TRIGGER_AFTER_DELETE_ENABLE <> FALSE. See sample sqlfiddle.

Therefore:

  • rows with 1 are inserted into table2 (variable is not set yet)
  • rows with 2 are not inserted into table2 (variable set to FALSE)

Output from table1:

d   name    level
0   a       0
2   c       0
4   f       0

Output from table2:

id  name    level
1   b       1
4   e       1

Row with level=2 is not here anymore (See sample sqlfiddle).

You basically choose like this:

  • @TRIGGER_AFTER_DELETE_ENABLE = FALSE before the delete statement => delete and copy data
  • @TRIGGER_AFTER_DELETE_ENABLE <> FALSE => only delete data

SQL Server

Although the question is not about this RDBMS, with SQL Server I would rather use the OUTPUT clause:

DELETE FROM t
OUTPUT deleted.id, deleted.name, deleted.level INTO table2
FROM table1 t
WHERE level > 0;

It simply inserts somewhere else the output of what has just been deleted. No temp table is needed and IO will be limited on tempdb.

| improve this answer | |
  • Thanks for the answer, however for my use case this isn't so great, since if I wanted to delete a row that I didn't want going into my main table, this would require some sort of workaround. – insaner Jul 12 '16 at 23:20
  • see update. you only need to set a variable and choose between delete/move or simply delete – Julien Vavasseur Jul 13 '16 at 6:40
1

For those curious, this is how I solved it, thanks to @evanSteinbrenner's tip:

LOCK TABLES
    main WRITE,
    main as t WRITE,
    main as t_in WRITE,
    main as df WRITE,
    main_date WRITE,
    main_date as d WRITE,
    main_date as d2 WRITE,
    main_date as dx WRITE,
    main_weekday WRITE,
    main_weekday as w WRITE;
CREATE TEMPORARY TABLE IF NOT EXISTS stacks_tmp AS ( $sql_stacks_ready );
SELECT COUNT(*) FROM stacks_tmp ORDER BY date;
INSERT INTO main (id, title, added_on, date) SELECT  id, title, added_on, date FROM  stacks_tmp;
DELETE FROM main_date WHERE  (id) IN (SELECT id FROM   stacks_tmp WHERE from_table = 'date') ;
DELETE FROM main_weekday WHERE  (id) IN (SELECT id FROM   stacks_tmp WHERE from_table = 'weekday') ;
UNLOCK TABLES;

This is just simplified out from the original perl/dbi code, which does some logic to check each statement for failure. I do not have it using transactions yet, since I still have yet to learn that. The $sql_stacks_ready variable contains my (semi) complex query that creates the ordered stack (and hence the need for all those aliases in the LOCK TABLES). The column from_table is set by the stack building query, and is set to whatever the originating table was.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.