SitePoint Sponsor

User Tag List

Results 1 to 13 of 13
  1. #1
    SitePoint Member
    Join Date
    Jun 2010
    Posts
    14
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    update in FROM clause

    I need to update isdone flag to 1 for ccallid is same like primaryoldcall or secondaryoldcall

    update ev_callcontrol set isdone = 1 where ccallid in (select primaryoldcall from ev_callcontrol where ccallid=18) or ccallid in (select secondaryoldcall from ev_callcontrol where ccallid=18)

    but I getting:

    Error Code : 1093
    You can't specify target table 'ev_callcontrol' for update in FROM clause
    (16 ms taken)

    could any body please help?

    thanks & regards
    mrnewbie

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,506
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Can you give us the table layout, and an example of what you want to do with some data?

  3. #3
    SitePoint Member
    Join Date
    Jun 2010
    Posts
    14
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    primaryoldcall & secondaryoldcall might have callid no
    fe.

    Code:
    callid   primaryoldcall    secondaryoldcall
    ==============================
    2
    3
    4        3                     2
    5
    6        3                     3
    I want to delete based on the callid = 4 and if the primaryoldcall or secondaryoldcall values are exists in callid then the callid data based on primaryoldcall or secondaryoldcall should be deleted.

    in the above example..
    I want to delete callid = 4 ..and it has primaryoldcall = 3 and secondaryoldcall = 2 ..
    so .. in this condition .it must also delete callid = 3 and callid = 2 ..
    any idea?



    Code:
    mysql> describe ev_callcontrol;
    +------------------+------------+------+-----+------------+----------------+
    | Field            | Type       | Null | Key | Default    | Extra          |
    +------------------+------------+------+-----+------------+----------------+
    | ccid             | int(11)    | NO   | PRI |            |                |
    | cdate            | date       | NO   | PRI | 0000-00-00 |                |
    | ctime            | time       | YES  |     | NULL       |                |
    | seq              | int(11)    | NO   | PRI | NULL       | auto_increment |
    | ccallid          | int(11)    | YES  |     | NULL       |                |
    | primaryoldcall   | int(11)    | YES  |     | 0          |                |
    | secondaryoldcall | int(11)    | YES  |     | 0          |                |
    | period           | char(6)    | YES  |     | NULL       |                |
    | isdone           | tinyint(1) | YES  |     | 0          |                |
    | isforcedone      | tinyint(1) | YES  |     | 0          |                |
    +------------------+------------+------+-----+------------+----------------+
    10 rows in set (0.04 sec)
    
    mysql>

  4. #4
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    73 Post(s)
    Tagged
    0 Thread(s)
    set up keys on the primaryoldcall and secondaryoldcall with ON DELETE CASCADE ?
    note: Deleteing row 3 in your example makes row 6 reference invalid entry...

  5. #5
    SitePoint Member
    Join Date
    Jun 2010
    Posts
    14
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    just to share..

    update ev_callcontrol set isdone =1 where ccallid in (select ccallid from (select * from ev_callcontrol where ccallid in (select primaryoldcall from ev_callcontrol where ccallid = 4) or ccallid in (select secondaryoldcall from ev_callcontrol where ccallid=4) or ccallid=4) as t1)

  6. #6
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    73 Post(s)
    Tagged
    0 Thread(s)
    okay.. i'm blind... read delete instead of update...
    If you're setting isdone = 1 on row 3, would that not imply that row 6 should be isdone too?

  7. #7
    SitePoint Member
    Join Date
    Jun 2010
    Posts
    14
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    no,
    first check ccallid .. then check if it has primaryoldcall or secondaryoldcall .. then compare with another ccallid that have the same value as primaryoldcall or secondaryoldcall
    in this case..
    I find ccallid = 4 and it has primaryoldcall = 3 and secondaryoldcall = 2.. and then delete ccallid = primaryoldcall and ccallid = secondaryoldcall

  8. #8
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    73 Post(s)
    Tagged
    0 Thread(s)
    'delete' meaning UPDATE row 4 to be blank in primaryoldcall and secondaryoldcall, or DELETE the primaryoldcall and secondaryoldcall rows?

    Deleting the rows seems like a bad idea, but...
    DELETE FROM ev_callcontroll WHERE (ccallid,ccallid) IN(SELECT * FROM (SELECT primaryoldcall,secondaryoldcall FROM ev_callcontrol WHERE ccallid = <youroriginalnumberhere>) AS temp);

  9. #9
    SitePoint Member
    Join Date
    Jun 2010
    Posts
    14
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    sorry, I mean update..not delete ..

    I find ccallid = 4 and it has primaryoldcall = 3 and secondaryoldcall = 2.. and then update isdone = 1 for ccallid = primaryoldcall and ccallid = secondaryoldcalle

  10. #10
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    73 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by mrnewbie View Post
    sorry, I mean update..not delete ..

    I find ccallid = 4 and it has primaryoldcall = 3 and secondaryoldcall = 2.. and then update isdone = 1 for ccallid = primaryoldcall and ccallid = secondaryoldcalle
    hehe now we're both doing it

    UPDATE ev_callcontroll SET isdone = 1 WHERE (ccallid,ccallid) IN(SELECT * FROM (SELECT primaryoldcall,secondaryoldcall FROM ev_callcontrol WHERE ccallid = <youroriginalnumberhere>) AS temp);
    (If you want to set isdone = 1 on the original row as well, add "OR ccallid = <youroriginalnumberhere>" on the end of the query)

  11. #11
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    73 Post(s)
    Tagged
    0 Thread(s)
    Yeah okay i screwed that up, AND instead of OR... silly row/column lineup

    Code MySQL:
    UPDATE ev_callcontroll SET isdone = 1 
    WHERE ccallid = (SELECT * FROM (SELECT primaryoldcall FROM ev_callcontrol WHERE ccallid = <youroriginalnumberhere>) AS temp)
    OR ccallid = (SELECT * FROM (SELECT secondaryoldcall FROM ev_callcontrol WHERE ccallid = <youroriginalnumberhere>) AS temp2)
    OR ccallid = <youroriginalnumberhere>;

    (and now one of the guru's will do it better than me :P)

  12. #12
    SitePoint Member
    Join Date
    Jun 2010
    Posts
    14
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks, StarLion.. you made it more simple ..heheh

  13. #13
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,323
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by StarLion View Post
    (and now one of the guru's will do it better than me :P)
    if you are referring to the WHERE clause (i did not really follow along on the thread), then yes
    Code:
    WHERE ccallid = <youroriginalnumberhere>
       OR ccallid IN ( 
          SELECT primaryoldcall 
            FROM ev_callcontrol 
           WHERE ccallid = <youroriginalnumberhere>
          UNION
          SELECT secondaryoldcall 
            FROM ev_callcontrol 
           WHERE ccallid = <youroriginalnumberhere> )
    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
  •