不好意思,我没有说清楚,我的意思是这句代码: query4.SQL.Add('insert into average(A0101,dept_id,average_cent) SELECT a.a0101, a.dept_id, AVG(a.true_cent * b.per) AS average_cent FROM standrd b,person a where b.dept_id = a.dept_id AND b.standard_name = a.standard_name GROUP BY a.a0101, a.dept_id');我要把insert into 换成 update,该怎么修改?
UPDATE AVERAGE SET average_cent = ( SELECT AVG(a.true_cent * b.per) AS average_cent FROM standrd b,person a where b.dept_id = a.dept_id AND b.standard_name = a.standard_name ) WHERE AVERAGE.A0101 = person.a0101 AND AVERAGE.dept_id= person.dept_id
像insert之类的,没有返回数据结果的操作用execsql。 反之,用open。
procedure TForm2.Button2Click(Sender: TObject); begin query4.Close; query4.SQL.Clear; query4.SQL.Add('insert into average(A0101,dept_id,average_cent) SELECT a.a0101, a.dept_id, AVG(a.true_cent * b.per) AS average_cent FROM standrd b,person a where b.dept_id = a.dept_id AND b.standard_name = a.standard_name GROUP BY a.a0101, a.dept_id'); query4.Open; close; end;我希望这段代码第一次运行的时候是将数据保存到空表中,以后运行只是将数据更新,请问该怎么修改?
我是一个菜鸟!还有就是用update该怎么修改?
query4.ExecSQL;
query4.SQL.Add('insert into average(A0101,dept_id,average_cent) SELECT a.a0101, a.dept_id, AVG(a.true_cent * b.per) AS average_cent FROM standrd b,person a where b.dept_id = a.dept_id AND b.standard_name = a.standard_name GROUP BY a.a0101, a.dept_id');我要把insert into 换成 update,该怎么修改?
SET average_cent = (
SELECT AVG(a.true_cent * b.per) AS average_cent
FROM standrd b,person a where b.dept_id = a.dept_id AND b.standard_name = a.standard_name
)
WHERE AVERAGE.A0101 = person.a0101 AND AVERAGE.dept_id= person.dept_id
反之,用open。
begin
query4.Close;
query4.SQL.Clear;
query4.SQL.Add('insert into average(A0101,dept_id,average_cent) SELECT a.a0101, a.dept_id, AVG(a.true_cent * b.per) AS average_cent FROM standrd b,person a where b.dept_id = a.dept_id AND b.standard_name = a.standard_name GROUP BY a.a0101, a.dept_id');
query4.Open;
close;
end;我希望这段代码第一次运行的时候是将数据保存到空表中,以后运行只是将数据更新,请问该怎么修改?
最简单的办法,删光了在重新insert,这样比较简单。
对了,不要用open,都是ExecSQL。
if 表=空 then
增加数据
else
更新数据;
还有记住要用execSQl(不用select时)