update in FROM clause

[mrnewbie]

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

[guido2004]

Can you give us the table layout, and an example of what you want to do with some data?

[mrnewbie]

primaryoldcall & secondaryoldcall might have callid no
fe.

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?

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>

[StarLion]

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...

[mrnewbie]

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)

[StarLion]

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?

[mrnewbie]

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

[StarLion]

'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);

[mrnewbie]

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

[StarLion]

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)

[StarLion]

Yeah okay i screwed that up, AND instead of OR... silly row/column lineup

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)

[mrnewbie]

Thanks, StarLion.. you made it more simple ..heheh

[r937]

(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

WHERE ccallid = <youroriginalnumberhere>
   OR ccallid IN ( 
      SELECT primaryoldcall 
        FROM ev_callcontrol 
       WHERE ccallid = <youroriginalnumberhere>
      UNION
      SELECT secondaryoldcall 
        FROM ev_callcontrol 
       WHERE ccallid = <youroriginalnumberhere> )