forum.lissyara.su

Код: Выделить всё

UPDATE test1
SET close = 1
WHERE id = (SELECT id
FROM
  test1
WHERE
  parent = :P
  
UNION
  
SELECT t1.id
FROM
  test1 t1 JOIN test1 t2
    ON t1.parent = t2.id
WHERE
  t2.parent = :P )

Код: Выделить всё

$ss = 'SELECT id_se 
FROM section
WHERE id = 1';

while ($id_se = $ss ) {

$ss2 = 'SELECT id_se
FROM
  section
WHERE
  parent_se_id = 13
  
UNION
  
SELECT t1.id_se
FROM
  section t1 JOIN section t2
    ON t1.parent_se_id = t2.id_se
WHERE
  t2.parent_se_id = 13';


while ( $id = $ss2 ) {


DELETE from t1,t2,t3,t4
where id = $id bla-lba-bla

}


}

Код: Выделить всё

  * Incorrectly used table in subquery:
 
      Error 1093 (ER_UPDATE_TABLE_USED)
      SQLSTATE = HY000
      Message = "You can't specify target table 'x'
      for update in FROM clause"
 
      This error will occur in cases like this:
 
      UPDATE t1 SET column2 = (SELECT MAX(column1) FROM t1);
 
      It's okay to use a subquery for assignment within an UPDATE statement, since subqueries are legal in UPDATE and DELETE statements as well as in
 SELECT statements. However, you cannot use the same table, in this case table t1, for both the subquery's FROM clause and the update target.  
 
Usually, failure of a subquery causes the entire statement to fail. 

Код: Выделить всё

UPDATE section AS t4 LEFT JOIN section t2 ON t4.parent_se_id = t2.id_se
SET t4.active_se = 1 - t4.active_se
WHERE t2.parent_se_id = 54 or t4.parent_se_id = 54 

Код: Выделить всё

		my $sql_par = 'SELECT id_se
							  FROM section
							WHERE
  					parent_se_id = '.$args[0].'
					  
					UNION
					  
					SELECT t1.id_se
					FROM
  					section t1 JOIN section t2
    			ON t1.parent_se_id = t2.id_se
					WHERE
			  t2.parent_se_id = '.$args[0];
		
 	 my $sth = $dbh->prepare($sql_par);
      	 $sth->execute();          
            
            while ( my $id = $sth->fetchrow_arrayref() ) {    
    
       
    			$dbh->do( 'DELETE from section        				   	
              	   	  WHERE id_se = '.$id->[0],
       		 	 undef, undef );        
       
    			$dbh->do( 'DELETE from comment        				   	
              	   	  WHERE id_se = '.$id->[0],
       		 	 undef, undef );
       		 	 
    			$dbh->do( 'DELETE from content        				   	
              	   	  WHERE id_se = '.$id->[0],
       		 	 undef, undef );       		 	        
       
				}
				
           $sth->finish();     
    


    			$dbh->do( 'DELETE from section        				   	
              	   	  WHERE id_se = '.$args[0],
       		 	 undef, undef );    
    			
   			$dbh->do( 'DELETE from comment        				   	
              	   	  WHERE id_se = '.$args[0],
       		 	 undef, undef );
       		 	 
    			$dbh->do( 'DELETE from content        				   	
              	   	  WHERE id_se = '.$args[0],
       		 	 undef, undef );  
     		 	 

Код: Выделить всё

CREATE  tree (i, parent_id i, name c(10))
INSERT INTO tree VALUES (1,0,'Деталь1')
INSERT INTO tree VALUES (2,0,'Деталь2')
INSERT INTO tree VALUES (3,2,'Деталь3')
INSERT INTO tree VALUES (4,2,'Деталь4')
INSERT INTO tree VALUES (5,4,'Деталь5')
INSERT INTO tree VALUES (6,4,'Деталь6')
INSERT INTO tree VALUES (7,5,'Деталь7')

Код: Выделить всё

DelTree (2)

PROCEDURE DelTree (tnID)
LOCAL laChilds[1], ln1
SELECT det_id FROM tree WHERE parent_id = m.tnID INTO ARRAY laChilds
FOR ln1 = 1 TO _TALLY
DelTree (laChilds[m.ln1])
ENDFOR
DELETE FROM tree WHERE det_id = m.tnID
ENDPROC