How to select and then delete immediately from a database?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
legend986
Forum Contributor
Posts: 258
Joined: Sun Jul 15, 2007 2:45 pm

How to select and then delete immediately from a database?

Post 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?
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

Two seperate queries?
User avatar
legend986
Forum Contributor
Posts: 258
Joined: Sun Jul 15, 2007 2:45 pm

Post 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...
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Three queries.. a select, update and delete. Joy.
User avatar
legend986
Forum Contributor
Posts: 258
Joined: Sun Jul 15, 2007 2:45 pm

Post 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?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Separate.
User avatar
legend986
Forum Contributor
Posts: 258
Joined: Sun Jul 15, 2007 2:45 pm

Post 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?
User avatar
Kieran Huggins
DevNet Master
Posts: 3635
Joined: Wed Dec 06, 2006 4:14 pm
Location: Toronto, Canada
Contact:

Post 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?
User avatar
legend986
Forum Contributor
Posts: 258
Joined: Sun Jul 15, 2007 2:45 pm

Post by legend986 »

I didn't understand that :(
User avatar
Kieran Huggins
DevNet Master
Posts: 3635
Joined: Wed Dec 06, 2006 4:14 pm
Location: Toronto, Canada
Contact:

Post by Kieran Huggins »

User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

Kieran Huggins wrote:"gotta keep 'em separated"
* Busts out into crazy guitar rift *
Post Reply