b_b:=OpenDialog1.FileName;
data_m.ADOQuery_persrec.ConnectionString := 'Provider=Microsoft.Jet.OLEDB.4.0;DataSource='+DataFile+';Persist Security Info=False;';
data_m.ADOQuery_persrec.SQL.Clear;
begin
//
sqltxt:='';
sqltxt:='delete * from xxsqxmk ';
sqltxt:=sqltxt+'where sfzh1 in (select sfzh1 from b in '''+b_b+''')';
data_m.ADOQuery_persrec.SQL.Add(sqltxt);
data_m.ADOQuery_persrec.ExecSQL;
//以上代码有问题,我想通过以上代码删除重复记录,事实上导致错误!
//以下代码正确,但不能删除重复记录
sqltxt:='';
sqltxt:= 'insert into xxsqxmk ';
sqltxt:=sqltxt+'select * from b in '''+b_b+'''';
data_m.ADOQuery_persrec.SQL.Add(sqltxt);
data_m.ADOQuery_persrec.ExecSQL;
end;
请各位哥哥姐姐帮忙解决!
data_m.ADOQuery_persrec.ConnectionString := 'Provider=Microsoft.Jet.OLEDB.4.0;DataSource='+DataFile+';Persist Security Info=False;';
data_m.ADOQuery_persrec.SQL.Clear;
begin
//
sqltxt:='';
sqltxt:='delete * from xxsqxmk ';
sqltxt:=sqltxt+'where sfzh1 in (select sfzh1 from b in '''+b_b+''')';
data_m.ADOQuery_persrec.SQL.Add(sqltxt);
data_m.ADOQuery_persrec.ExecSQL;
//以上代码有问题,我想通过以上代码删除重复记录,事实上导致错误!
//以下代码正确,但不能删除重复记录
sqltxt:='';
sqltxt:= 'insert into xxsqxmk ';
sqltxt:=sqltxt+'select * from b in '''+b_b+'''';
data_m.ADOQuery_persrec.SQL.Add(sqltxt);
data_m.ADOQuery_persrec.ExecSQL;
end;
请各位哥哥姐姐帮忙解决!
from yourtable
delete yourtable
insert into yourtabe select * from #table
2,用adoquery2删除记录
3,把临时表导入
可是我确实是girl呀
data_m.ADOQuery_persrec.ConnectionString := 'Provider=Microsoft.Jet.OLEDB.4.0;DataSource='+DataFile+';Persist Security Info=False;';
data_m.ADOQuery_persrec.SQL.Clear;
begin
//
sqltxt:='';
sqltxt:='delete * from xxsqxmk ';
sqltxt:=sqltxt+'where sfzh1 in (select sfzh1 from b in '''+b_b+''''+')';//此处修改了
data_m.ADOQuery_persrec.SQL.Add(sqltxt);
data_m.ADOQuery_persrec.ExecSQL;
//以上代码有问题,我想通过以上代码删除重复记录,事实上导致错误!
//以下代码正确,但不能删除重复记录
sqltxt:='';
sqltxt:= 'insert into xxsqxmk ';
sqltxt:=sqltxt+'select * from b in '''+b_b+'''';
data_m.ADOQuery_persrec.SQL.Add(sqltxt);
data_m.ADOQuery_persrec.ExecSQL;
end;
请各位哥哥姐姐帮忙解决!
sqltxt:='delete * from xxsqxmk ';改为
sqltxt:='delete from xxsqxmk ';
sqltxt:='delete from xxsqxmk ';//还是提示失败呀
begin
b_b:=OpenDialog1.FileName;
data_m.ADOQuery_persrec.ConnectionString := 'Provider=Microsoft.Jet.OLEDB.4.0;DataSource='+DataFile+';Persist Security Info=False;'; data_m.ADOQuery_persrec.SQL.Clear;
data_m.ADOQuery_persrec.sql.Add('select distinct * into #xxsqxmk'+
'from xxsqxmk '+
'delete xxsqxmk'+
'insert into xxsqxmk select * from #xxsqxmk'); data_m.ADOQuery_persrec.ExecSQL;
end;试试看
begin
adoquery1.Close;
adoquery1.SQL.Clear;
adoquery1.SQL.Add('select * from b where sfzh1 in (select sfzh1 from b group by sfzh1 having count(*)>1)');
adoquery1.Open;
end;procedure TForm1.Button2Click(Sender: TObject);
begin
adoquery1.Close;
adoquery1.SQL.Clear;
adoquery1.SQL.Add('select * from b ');
adoquery1.Open;
end;procedure TForm1.Button3Click(Sender: TObject);
begin
adoquery1.Close;
adoquery1.SQL.Clear;
adoquery1.SQL.Add('delete from b where sfzh1 in (select sfzh1 from b group by sfzh1 having count(*)>1)');
adoquery1.ExecSQL;
adoquery1.Close;
adoquery1.SQL.Clear;
adoquery1.SQL.Add('select * from b ');
adoquery1.Open;
end;procedure TForm1.FormCreate(Sender: TObject);
begin
ADOConnection1.Connected:=true;
end;procedure TForm1.Button4Click(Sender: TObject);
begin
adoquery1.Close;
adoquery1.SQL.Clear;
adoquery1.SQL.Add('alter table b add idMytest int identity(1,1)');
adoquery1.ExecSQL; adoquery1.Close;
adoquery1.SQL.Clear;
adoquery1.SQL.Add('delete from b where sfzh1 in (select sfzh1 from b where b.idMytest<idMytest '+
'group by sfzh1 having count(*)>1)');
adoquery1.ExecSQL; adoquery1.Close;
adoquery1.SQL.Clear;
adoquery1.SQL.Add('alter table b drop column idMytest');
adoquery1.ExecSQL;end;
你到底让我怎么招啊????????”
这不是很多人吗,
不过,高手现在都很忙,没时间来CSDN啊!
各位见量
delete * from xxsqxmk where sfzh1 in (select sfzh1 from b in '''+b_b+''')';
其中b_b变量无论是什么内容,但拼起来都不符合SQL语法,
如果你把你把问题本身描述清楚一点(表中的字段,要达到的具体目的),
我想即或不是星级,一样搞定,嘿嘿!!
不信 mailto: [email protected]
先把没有重复的记录插入一个临时表:
sqlTxt:='Insert TempTable select distinct * from xxsqxmk';
然后把原来的记录删除:
sqlTxt:='Delete * from xxsqxmk';//如果是Sqlserver数据库则应用'Delete from xxsqxmk';
再将临时表的记录插回来:
sqlTxt:='Insert xxsqxmk select * from TempTable'这样就OK了!
select sfzh1 from b in '''+b_b+''' //--------???有这样的sql?
http://community.csdn.net/Expert/topic/3826/3826686.xml?temp=.600979
1,用adoquery1选择不重复的记录保存到临时表
2,用adoquery2删除记录
3,把临时表导入
有主键:pers_id,---------------------------
有主键的表怎么会写进了重复记录????
对于每一个b表中的值,在xxsqxmk中查找有没有重复,如有重复则更新,否则插入
先用一个ADOQuery1打开b表
假设xtxxk和b表中有两个字段
sfzhl 主键 字符型
sl 数字型b_b:=OpenDialog1.FileName;ADOQuery1.Close;data_m.ADOQuery_persrec.ConnectionString := 'Provider=Microsoft.Jet.OLEDB.4.0;DataSource='+DataFile+';Persist Security Info=False;';//Query1要连接的数据库b_b
data_m.ADOQuery1.ConnectionString := 'Provider=Microsoft.Jet.OLEDB.4.0;DataSource='+b_b+';Persist Security Info=False;';ADOQuery1.Clear;
ADOQuery1.SQL.Add('select * from b');
ADOQuery1.Open;//对于b表中的每一条记录
while not ADOQuery1.Eof do
begin
data_m.ADOQuery_persrec.SQL.Clear;
data_m.ADOQuery_persrec.SQL.Add('select count(*) from xtxxk where sfzhl='''+ADOQuery1.FieldByName('sfzhl').AsString+'''');//sfzhl假定为字符型
data_m.ADOQuery_persrec.Open; //xtxxk中是否存在此记录
if data_m.ADOQuery_persrec.Fields[0].AsInteger=0 then //不存在插入
begin
data_m.ADOQuery_persrec.Close;
data_m.ADOQuery_persrec.Clear;
data_m.ADOQuery_persrec.SQL.Add('Insert into xtxxk(sfzhl,sl) values('''+ADOQuery1.FieldByName('sfzhl').AsString+''','+ADOQuery1.FieldByName('sl').AsString+')');
data_m.ADOQuery_persrec.ExecSQL;
end
else//存在,更新
begin
data_m.ADOQuery_persrec.Close;
data_m.ADOQuery_persrec.Clear;
data_m.ADOQuery_persrec.SQL.Add('update xtxxk set sl=ADOQuery1.FieldByName('sl').AsString+' where sfzhl='''+ADOQuery1.FieldByName('sfzhl').AsString+''' );
data_m.ADOQuery_persrec.ExecSQL;
end;
Next;
end;
data_m.ADOQuery_persrec.ExecSQL;来实现吧