Update in FROM clause

Databases
1

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

if you are referring to the WHERE clause (i did not really follow along on the thread), then yes :slight_smile:

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

Thanks, StarLion… you made it more simple …heheh

4

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)

5

hehe now we’re both doing it :wink:

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)

6

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

7

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

8

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

9

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?

10

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)

11

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

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…

13

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