Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2005
    Posts
    76

    Unanswered: delete from joined table

    How do I delete from a joined table....

    I want to delete all the records from transaction AND order table where status is > 4

    sumfing like:


    DELETE ORDERS.*, TRANSACTIONS.*
    from ORDERS
    INNER JOIN TRANSACTIONS
    where ORDERS.order_no = TRANSACTIONS.order_no
    and status > 4

    but doesnt seem 2 like dat???

    THANKS

  2. #2
    Join Date
    Dec 2005
    Posts
    266
    DELETE FROM Transactions WHERE Transactions .order_no = 4
    DELETE FROM Orders WHERE ORDERS.order_no = 4

    or ON DELETE CASCADE when creating your table

  3. #3
    Join Date
    Feb 2005
    Posts
    76
    But status is only is a column only in the order table...

    Never heard of delete casade?!

    was finking bout it last night, would this work...

    create a view:

    Create View trandelete_data
    AS
    SELECT * transactions
    INNER JOIN orders on transactions.order_no = orders .order_no
    where status >4

    GO

    DELETE FROM trandelete_data


    would that work???

    Thanks
    Last edited by STUCK1234; 12-21-06 at 05:21.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by STUCK1234
    would that work???
    what happened when you tested it?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Feb 2005
    Posts
    76
    doesnt wrk!!!

    Lol.... y do I make things so completed!!

    Answer:

    DELETE
    from TRANSACTIONS
    where order_no in
    (select order_no from ORDERS where status > 6)
    Last edited by STUCK1234; 12-21-06 at 11:56.

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    No need for the IN statement with its nested query...

    DELETE TRANSACTIONS
    from TRANSACTIONS
    inner join ORDERS on TRANSACTIONS.order_no = ORDERS.order_no
    where ORDERS status > 6
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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