SitePoint Sponsor

User Tag List

Results 1 to 15 of 15
  1. #1
    SitePoint Wizard PHPycho's Avatar
    Join Date
    Dec 2005
    Posts
    1,201
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question deleting multiple rows prob???

    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

  2. #2
    SitePoint Enthusiast manish_er's Avatar
    Join Date
    Oct 2005
    Location
    Delhi : India
    Posts
    77
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  3. #3
    SitePoint Wizard
    Join Date
    Jul 2003
    Location
    Kent
    Posts
    1,921
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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.

  4. #4
    SitePoint Wizard PHPycho's Avatar
    Join Date
    Dec 2005
    Posts
    1,201
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,336
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by PHPycho
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Wizard PHPycho's Avatar
    Join Date
    Dec 2005
    Posts
    1,201
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Plz help with the FUll delete query

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,336
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    which FULL delete query?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Wizard PHPycho's Avatar
    Join Date
    Dec 2005
    Posts
    1,201
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    then you can use the ON DELETE CASCADE feature, or write a trigger, if you feel comfortable doing that
    using above

  9. #9
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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.

  10. #10
    SitePoint Wizard PHPycho's Avatar
    Join Date
    Dec 2005
    Posts
    1,201
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question my problem in detail.....

    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
    Code:
    |fn_id|user_id|fren_id|block_status|
    |1     |1        |2       | 0               |
    |2     |2        |1       | 0               |
    frensrequest
    Code:
    |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

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,336
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  12. #12
    SitePoint Wizard PHPycho's Avatar
    Join Date
    Dec 2005
    Posts
    1,201
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question my problem in detail.....

    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
    Code:
    |fn_id|user_id|fren_id|block_status|
    |1     |1        |2       | 0               |
    |2     |2        |1       | 0               |
    frensrequest
    Code:
    |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

  13. #13
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,336
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    hello? you just repeated your previous post
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  14. #14
    SitePoint Wizard PHPycho's Avatar
    Join Date
    Dec 2005
    Posts
    1,201
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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................??

  15. #15
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,336
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by PHPycho
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •