deleting multiple rows prob???

[PHPycho]

Hello Friends of Sitepoint
I dont know the query for multiple row delete......
I want to delete two rows of a table using WHERE clause in a single query...
Plz help me with such query
...any help would be greatle appreciated

[manish_er]

for deleteing multiple rows using have to give some crieteria in WHERE clause like:

delete from table1 where status='2'

here your database have a field 'status' on the basis of which you are deleting rows.
Here all rows which have their status field=2 are deleted in one go.

[Dr John]

Just remember that you have to be really sure it will only delete the exact records you want to get rid of. It could be a bit upsetting if when you set out to delete two records it says 122 records deleted....I'd suggest first you try an ordinary select using the same where condition to be sure exactly which records are returned, before risking the delete.

[PHPycho]

Thanks for the quick reply....
I got my problem solved..
Now again i want to arise a new Question
Question: How to delete data from two different tables........??
Any help/suggestion would be very appreciated...
Thank u in advance

[r937]

How to delete data from two different tables........??

if the rows are related by primary/foreign key, then you can use the ON DELETE CASCADE feature, or write a trigger, if you feel comfortable doing that

otherwise, use two DELETE statements

[PHPycho]

Plz help with the FUll delete query

[r937]

which FULL delete query?

[PHPycho]

then you can use the ON DELETE CASCADE feature, or write a trigger, if you feel comfortable doing that

using above

[longneck]

actually, in mysql that's not possible. triggers in mysql can only affect the row in question and can not reference other rows or other tables.

i suggest taking the delete privelege away from the mysql user and using a stored procedure instead.

[PHPycho]

Now i am going to tell in detail about my Problem...
I had two tables 'frensrequest' and 'frensnetwork' with the following fields and values(assumed).
frensnetwork

|fn_id|user_id|fren_id|block_status|
|1     |1        |2       | 0               |
|2     |2        |1       | 0               |

frensrequest

|req_id|req_to|req_from|pending_status|
|1      |2       |1           | 1                |

I had made a Edit friends Section where users can edit friends in their network(like hi5) .
Let us suppose i am the user with user_id=1 .i want to delete friend with fren_id=2...on successful delete operation it should delete two rows from 'frensnetwork' with user_id=1 or 2 and fren_id=2 or 1 ie two rows with fn_id=1 and 2...
at the same time it should delete one row from the 'frenrequest' with req_to=2 and pending_status=1....
How to peform the single query to delete two rows from 'frensnetwork' and one from 'frenrequest' ...
I hope my problem is clear to you.....
I am waiting eagerously for the reply from u.......
again i want to give thanks in advance

[r937]

before you get involved in writing a trigger, go back and re-examine your data model

if john is user 1 and mary is user 2, then user_id=1,fren_id=2 means john loves mary and user_id=2,fren_id=1 means mary loves john

if john decides he doesn't love mary any more, why do you want to delete both rows? maybe mary still loves john? and why delete, why not just update the block status to block?

and what does req_to=2,req_from=1 mean? mary wants to love john but needs his approval? and why a separate table? why not blend the request table into the first table with an additional pending status column? and why delete? why not update the pending status to not pending?

these are the types of questions that you might want to think about before you jump into writing database code which you've never written before, which is very tricky to test, and which might not gain you any measurable performance improvement over two simple consecutive delete queries

[PHPycho]

Now i am going to tell in detail about my Problem...
I had two tables 'frensrequest' and 'frensnetwork' with the following fields and values(assumed).
frensnetwork

|fn_id|user_id|fren_id|block_status|
|1     |1        |2       | 0               |
|2     |2        |1       | 0               |

frensrequest

|req_id|req_to|req_from|pending_status|
|1      |2       |1           | 1                |

I had made a Edit friends Section where users can edit friends in their network(like hi5) .
Let us suppose i am the user with user_id=1 .i want to delete friend with fren_id=2...on successful delete operation it should delete two rows from 'frensnetwork' with user_id=1 or 2 and fren_id=2 or 1 ie two rows with fn_id=1 and 2...
at the same time it should delete one row from the 'frenrequest' with req_to=2 and pending_status=1....
How to peform the single query to delete two rows from 'frensnetwork' and one from 'frenrequest' ...
I hope my problem is clear to you.....
I am waiting eagerously for the reply from u.......
again i want to give thanks in advance

[r937]

hello? you just repeated your previous post

[PHPycho]

pardon me
i want to clearify the tables.............
frenrequest
if the user browses the friends and wants to add ......then entry goes to frensrequest table with pending status=0......when that user logs in and gets the requests...if he accepts the request then pending status is set to 1 and at the same time data entry goes to frensnetwork in two rows for example 1-2 and 2-1.....................
Your idea is excellent but I want to do it it this way...........
Will you please help with the neccessay query................??

[r937]

if he accepts the request then pending status is set to 1 and at the same time data entry goes to frensnetwork in two rows for example 1-2 and 2-1

okay, to delete, you just run the reverse of this

if it took you 2 queries to add the data, use 2 queries to delete it