56,678
社区成员
发帖
与我相关
我的任务
分享
mysql> delete a from tb a,(select step from tb where ID=5654) b where instr(a.Step,b.Step)=1 ;
Query OK, 1 row affected (0.06 sec)
mysql> select * from tb;
+-------+--------+-------------+
| id | step | name |
+-------+--------+-------------+
| 54325 | 101 | 江苏省 |
| 41234 | 101001 | 江苏省县城1 |
| 8687 | 101003 | 江苏省县城3 |
+-------+--------+-------------+
3 rows in set (0.00 sec)
mysql>
delete a from tb a,(select step from tb where ID=431) b where instr(a.Step,b.Step)=1 ;
mysql> select * from tb;
+--------+--------+-------------+
| id | step | name |
+--------+--------+-------------+
| 431 | 100 | 安徽省 |
| 53425 | 100001 | 安徽省县城1 |
| 412123 | 100002 | 安徽省县城2 |
| 54325 | 100003 | 安徽省县城3 |
| 54325 | 101 | 江苏省 |
| 41234 | 101001 | 江苏省县城1 |
| 5654 | 101002 | 江苏省县城2 |
| 8687 | 101003 | 江苏省县城3 |
+--------+--------+-------------+
8 rows in set (0.00 sec)
mysql> delete a from tb a,(select step from tb where ID=431) b where instr(a.Step,b.Step)=1 ;
Query OK, 4 rows affected (0.06 sec)
mysql> select * from tb;
+-------+--------+-------------+
| id | step | name |
+-------+--------+-------------+
| 54325 | 101 | 江苏省 |
| 41234 | 101001 | 江苏省县城1 |
| 5654 | 101002 | 江苏省县城2 |
| 8687 | 101003 | 江苏省县城3 |
+-------+--------+-------------+
4 rows in set (0.00 sec)
mysql>
建表如下: 求DELETE语句
create table tb
(
id varchar(10),
step varchar(10),
name varchar(20)
)
insert into tb values(431 , 100,'安徽省')
insert into tb values(53425 , 100001 '安徽省县城1')
insert into tb values(412123 , 100002 '安徽省县城2')
insert into tb values(54325 , 100003 '安徽省县城3')
insert into tb values(54325 , 101 '江苏省')
insert into tb values(41234 , 101001 '江苏省县城1')
insert into tb values(5654 , 101002 '江苏省县城2')
insert into tb values(8687 , 101003 '江苏省县城3')
ID是主键,自动生成的。我能得到ID的值,但是得不到Step的值,所以我要通过ID来得到Step的值,
因为我删除的要求是 当我删除Step=100的这一行,那么它的下属单位100001,100002,100003也会被删除。那么这就可能用到 DELETE FROM tb where step LIKE '100%'
我需要得到类似这样的语句
delete from tb where STEP=? 是要带问号的语句
我写的是这样的,但是是错误的
<DeleteCommands>
<SQL>
<CommandText>delete from G_Menu where Step like concat((select Step from g_menu where id = ?ID),'%')</CommandText>
<ParameterCollection>
<Parameter Name="ID" Type="Long"/>
</ParameterCollection>
</SQL>
</DeleteCommands>