两个表A,B
A: bm B: bm
dwbm dwbm
xs jfjs
问题:1\想把满足b.bm=A.bm and b.dwbm=A.dwbm and A.xs='1' 条件的表b的记录的jfjs(数值形)增加10 如何写 update语句,
2\用的是Access数据库,Access数据库是否有日志文件,对数据库的更改是否记录在日志文件中?
A: bm B: bm
dwbm dwbm
xs jfjs
问题:1\想把满足b.bm=A.bm and b.dwbm=A.dwbm and A.xs='1' 条件的表b的记录的jfjs(数值形)增加10 如何写 update语句,
2\用的是Access数据库,Access数据库是否有日志文件,对数据库的更改是否记录在日志文件中?
2.没有日志文件,更改记录不会在日志中,要自己用语句去做.
set jfjs=jfjs+10
From b
left join a On b.bm=A.bm and b.dwbm=A.dwbm and a.xs='1'
where a.bm is not null and a.dwbm is not null //对应a为空的记录不符合条件,不进行更新。
update b
set jfjs=jfjs+10
From b
left join a On b.bm=A.bm and b.dwbm=A.dwbm and a.xs='1'
where a.bm is not null and a.dwbm is not null
update b
set jfjs=jfjs+10
From b
left join a On b.bm=A.bm and b.dwbm=A.dwbm and a.xs=''1''
where a.bm is not null and a.dwbm is not null //对应a为空的记录不符合条件,不进行更新。
query1.Sql.Add(format('SET njfjs=njfjs+''%s''',[Edit1.text]));
query1.Sql.Add('FROM Dw_grzh LEFT JOIN Dw_zgxx On Dw_grzh.rybm=Dw_zgxx.bm AND');
query1.Sql.Add('Dw_grzh.dwbm=Dw_zgxx.dwbm AND Dw_zgxx.sfzcjf=''1''');
query1.Sql.Add('AND Dw_zgxx.ygxs=''1''');
用format做,看起来清晰很多。
*************************************************************
to victor_yang(victoryang
非常感谢,你的这个我看不太明白,我上面这个语句前半部分应该没什么错误的,好象是inner join后面有错误了,请指出,谢谢了
实际效果是:Dw_zgxx.sfzcjf=''1'' AND Dw_zgxx.ygxs=''1''
应该在后面再加个单引号。Dw_zgxx.sfzcjf='+quotedstr('1')+' AND Dw_zgxx.ygxs='+quotedstr('1')+''''
'UPDATE DW_grzh SET njfjs=njfjs+'+Edit1.text+' FROM Dw_grzh LEFT JOIN Dw_zgxx On Dw_grzh.rybm=Dw_zgxx.bm AND Dw_grzh.dwbm=Dw_zgxx.dwbm AND Dw_zgxx.sfzcjf='+quotedstr('1')+' AND Dw_zgxx.ygxs='+quotedstr('1')从from以后好象就不对了,急啊,....................................
s:='UPDATE DW_grzh SET njfjs=njfjs+'+Edit1.text+' FROM Dw_grzh LEFT JOIN Dw_zgxx On Dw_grzh.rybm=Dw_zgxx.bm AND Dw_grzh.dwbm=Dw_zgxx.dwbm AND Dw_zgxx.sfzcjf='''+quotedstr('1')+''' AND Dw_zgxx.ygxs='''+quotedstr('1')+'''';
Query.clear;
Query.sql.add(s);
//注意在delphi里''在sql里是';