确定遍历一个表并更新另一个表的最佳方法
我正在使用 Delphi 7、BDE 和 Interbase(测试)、Oracle(生产)。
我有两个表(主表、响应表),
我需要单步执行响应表,使用其 Master_Id 字段在主表(id)中查找匹配记录,并使用主表中的日期字段更新主表中的日期字段响应表
这可以在 SQL 中完成吗?还是我实际上必须创建两个 TTable 或 TQueries 并逐步遍历每条记录?
示例:
打开两个表(Table1,Table2)
with Table1 do
begin
first;
while not EOF do
begin
//get master_id field
//locate in id field in table 2
//edit record in table 2
next;
end;
end;
谢谢
I am using Delphi 7, BDE, and Interbase (testing), Oracle (Production).
I have two tables (Master, Responses)
I need to step through the Responses table, use its Master_Id field to look it up in Master table (id) for matching record and update a date field in the Master table with a date field in the Responses table
Can this be done in SQL, or do i actually have to create two TTables or TQueries and step through each record?
Example:
Open two tables (Table1, Table2)
with Table1 do
begin
first;
while not EOF do
begin
//get master_id field
//locate in id field in table 2
//edit record in table 2
next;
end;
end;
thanks
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(2)
对 Chris 的查询稍作修改,加入一个 where 子句以仅选择需要更新的记录。否则,它将把其余日期设置为 NULL
更新为使用别名以避免混淆哪个列来自哪个表。
这不是现成的、可复制粘贴的查询,因为 UPDATE 语法因数据库而异。
您可能需要查阅数据库 sql 参考以了解 UPDATE 语句语法中的 JOIN。
当对我使用 MAX() 的同一个主条目有多个响应时,
您可以使用适合您业务的任何内容。
再次投入一些时间来理解 SQL。还不到几天的功夫。如果您对 Oracle 感兴趣,请获取 PLSQL 完整参考
One slight modification to Chris' query, throw in a where clause to select only the records that need the update. Otherwise it will set the rest of the dates to NULL
Updated to use aliases to avoid confusion which col comes from which table.
This is not ready made, copy-past'able query as UPDATE syntax differs from database to database.
You may need to consult your database sql reference for JOIN in UPDATE statement syntax.
When there are multiple responses to same master entry
I used MAX() you can use whatever suits your business.
Again invest some time understanding SQL. Its hardly a few days effort. Get PLSQL Complete reference if you are into Oracle
尝试这个 SQL(更改名称以适合您的情况)
UPDATE Master m
SET date =(从响应中选择日期,其中 id = m.id )
Try this SQL (changing names to fit your situation)
UPDATE Master m
SET date = ( SELECT date FROM Responses WHERE id = m.id )