Page 1 of 1

How to select and then delete immediately from a database?

Posted: Sat Oct 27, 2007 6:30 pm
by legend986
I am performing some operation like:

Code: Select all

UPDATE table2 SET column1_table2 = ( SELECT column1_table1
FROM table1 WHERE column2_table1 = column2_table2 LIMIT 1)
LIMIT 2 
So what I'm trying to do is to find a corresponding value and then I'm adding it into table2. Now how can I actually delete this value after this has been added to table2 immediately?

Posted: Sat Oct 27, 2007 6:32 pm
by John Cartwright
Two seperate queries?

Posted: Sat Oct 27, 2007 6:34 pm
by legend986
Well I don't know how to write that... I'm not storing any information in any variable right? What I want is once it inserts it has to delete that particular one from the other table...

Posted: Sat Oct 27, 2007 6:37 pm
by feyd
Three queries.. a select, update and delete. Joy.

Posted: Sat Oct 27, 2007 6:40 pm
by legend986
Oh where should I add the delete statement?

Code: Select all

UPDATE table2 SET column1_table2 = ( SELECT column1_table1
FROM table1 WHERE column2_table1 = column2_table2 LIMIT 1 AND DELETE column1_table1
FROM table1 WHERE column2_table1 = column2_table2 LIMIT 1)
LIMIT 2
Is that the way?

Posted: Sat Oct 27, 2007 6:44 pm
by feyd
Separate.

Posted: Sat Oct 27, 2007 7:02 pm
by legend986
Actually one another doubt I had was.. When I have to do many such operations (typically on a million records)... I mean first lookup for a value in table2 and then add it to table1, will it help to actually delete those values from the table so that I need not have to iterate over them everytime?

Posted: Sat Oct 27, 2007 7:03 pm
by Kieran Huggins
"gotta keep 'em separated"

Code: Select all

INSERT INTO brain (tune_cootie)
Maybe you should be using one table? Or one table PLUS an index table?

Posted: Sat Oct 27, 2007 7:05 pm
by legend986
I didn't understand that :(

Posted: Sat Oct 27, 2007 7:08 pm
by Kieran Huggins

Posted: Sat Oct 27, 2007 7:33 pm
by John Cartwright
Kieran Huggins wrote:"gotta keep 'em separated"
* Busts out into crazy guitar rift *