form1.ADOQuery1.SQL.Text:='update information set flg= s_grade + (select ID from department where substring(s_class,3,1)=s_department) + right(s_class,1)';
form1.ADOQuery1.ExecSQL;表结构如下:
information: s_number(key) s_name s_grade s_class s_tel
department: id s_department
提示错误:语法错误,在查询表达式中:s_grade + (select ID from department where substring(s_class,3,1)=s_department) + right(s_class,1)我在SQL语句在SQL SERVER中运行的很好!
form1.ADOQuery1.ExecSQL;表结构如下:
information: s_number(key) s_name s_grade s_class s_tel
department: id s_department
提示错误:语法错误,在查询表达式中:s_grade + (select ID from department where substring(s_class,3,1)=s_department) + right(s_class,1)我在SQL语句在SQL SERVER中运行的很好!
其次你加个括号试试
update information set flg=(s_grade + (select ID from department where substring(s_class,3,1)=s_department) + right(s_class,1))
2. where substring(s_class,3,1)=s_department 应写为 where substring(information.s_class,3,1)=department.s_department
写成这样就没问题,为什么啊?我中间的(select id from department where mid(information.s_class,3,1)=department.s_department)这个查询语句有错???
form1.ADOQuery1.SQL.Text := 'update information set flg = s_grade + ISNULL((select ID from department where substring(s_class, 3, 1) = s_department), '') + right(s_class,1)';另外, department表中的ID字段是什么类型的?
查询出来的,放在一个变量里面,然后再和'update information set flg ....组合,不要在一个ADOQuery1.SQL.Text 里放两条SQL SELECT查询语句....
create procedure prname
@xx varchar(20) --注意@xx是你要传过来的参数,还要和下面的的组合..
as
declare @sqlset @sql='update information set flg= s_grade + (select ID from department where substring(s_class,3,1)=s_department) + right(s_class,1)'
exec(@sql)