쉽

subquery 전문가 함 보세요

select *  from c_deal where de_mb_id='admin' and de_t='5' and de_jo_name = (select  de_jo_name   FROM c_deal WHERE `de_mb_id` ='admin'  AND `de_t` ='5' group by de_jo_name having sum(de_qtyB)-sum(de_qtyS) < 1 )
 
요러면 원하는 리스트 나오는데
요걸
 
delete  from c_deal where de_mb_id='admin' and de_t='5' and de_jo_name = (select  de_jo_name   FROM c_deal WHERE `de_mb_id` ='admin'  AND `de_t` ='5' group by de_jo_name having sum(de_qtyB)-sum(de_qtyS) < 1 )
하면
 
MySQL 메시지: 
#1093 - You can't specify target table 'c_deal' for update in FROM clause
 
게 되는 이유 아시는분 ....

 

댓글 3개

상황별로 어떤지 모르겠네요. 일단 스키마랑 샘플 데이타를 올려주시면 좀더 명확하게 체크 될것 같네요.

체크해봐야 할 부분
1. delete 조건절에 subquery 가능 여부와 subquery 에 group by, having 가능 여부
- select 랑 delete랑 다를수 있음..
2. select 에 max를 써봄
3. de_jo_name = 을 de_jo_name in 조건으로 변경해봄
자기 자신을 참조하여 insert,update,delete 할경우.. 발생하는 오류입니다.
해당 서브쿼리를 한번 더 감싸주시면 됩니당..


DELETE from c_deal
WHERE de_mb_id='admin'
AND de_t='5'
AND de_jo_name = (select T1.de_jo_name
from ( select de_jo_name
from c_deal
where `de_mb_id` ='admin'
and `de_t` ='5'
group by de_jo_name
having sum(de_qtyB)-sum(de_qtyS) < 1) T1)
요런식으로...
댓글을 작성하시려면 로그인이 필요합니다.

프로그램

8,187건
+
제목 글쓴이 날짜 조회
11-09-19 조회 2,597
11-09-19 조회 2,520
11-09-19 조회 5,538
11-09-19 조회 6,175
11-09-16 조회 2,887
11-09-16 조회 3,155
11-09-16 조회 6,351
11-09-15 조회 3,117
11-09-15 조회 2,880
11-09-15 조회 2,549
11-09-15 조회 2,544
11-09-15 조회 2,748
11-09-15 조회 2,705
11-09-15 조회 2,823
11-09-14 조회 2,735
11-09-14 조회 3,291
11-09-14 조회 2,403
11-09-14 조회 2,328
11-09-14 조회 2,316
11-09-13 조회 2,498
11-09-10 조회 2,968
11-09-10 조회 2,585
11-09-09 조회 3,170
11-09-09 조회 3,037
11-09-09 조회 3,131
11-09-09 조회 3,164
11-09-08 조회 3,849
11-09-08 조회 5,044
11-09-08 조회 3,440
11-09-08 조회 4,370
11-09-08 조회 3,874
11-09-08 조회 3,679
11-09-08 조회 2,773
11-09-08 조회 2,345
11-09-08 조회 2,750
11-09-08 조회 2,872
11-09-07 조회 3,534
11-09-07 조회 3,593
11-09-07 조회 3,014
11-09-07 조회 2,716
11-09-07 조회 2,809
11-09-07 조회 2,835
11-09-07 조회 3,069
11-09-07 조회 2,337
11-09-07 조회 2,434
11-09-07 조회 2,597
11-09-07 조회 3,537
11-09-07 조회 2,808
11-09-06 조회 2,634
11-09-06 조회 2,923
11-09-06 조회 2,484
11-09-05 조회 2,698
11-09-05 조회 2,645
11-09-05 조회 2,926
11-09-05 조회 2,441
11-09-04 조회 2,411
11-09-04 조회 2,374
11-09-03 조회 2,549
11-09-03 조회 2,708
11-09-02 조회 3,428
11-09-02 조회 2,805
11-09-02 조회 2,673
11-09-02 조회 3,582
11-09-02 조회 2,612
11-09-02 조회 2,500
11-09-02 조회 2,499
11-09-01 조회 2,700
11-09-01 조회 2,847
11-09-01 조회 2,722
11-09-01 조회 2,729
11-09-01 조회 2,874
11-09-01 조회 2,735
11-09-01 조회 4,173
11-09-01 조회 3,776
11-09-01 조회 4,443
11-08-31 조회 2,328
11-08-31 조회 2,631
11-08-31 조회 2,915
11-08-31 조회 2,633
11-08-31 조회 2,490
11-08-31 조회 3,300
11-08-30 조회 3,037
11-08-30 조회 4,267
11-08-29 조회 3,027
11-08-29 조회 2,611
11-08-29 조회 5,245
11-08-29 조회 3,373
11-08-29 조회 2,765
11-08-29 조회 3,117
11-08-28 조회 2,511
11-08-28 조회 3,662
11-08-28 조회 2,624
11-08-28 조회 2,988
11-08-26 조회 2,520
11-08-26 조회 3,026
11-08-26 조회 3,263
11-08-26 조회 2,760
11-08-25 조회 2,596
11-08-25 조회 2,908
11-08-25 조회 2,630