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"
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 *