Ms替换问题 [英] Ms Access replace issue

问题描述

所以，我必须更新一个数据库6000行重复。复制品是所有共享公共号码的公司。我的问题是，我只是一次更换一个或两个字段，即公司的网站，2009年提供，但他们没有在2010年。这是所有需要更换。我是一个进入菜鸟，我用手做所有的线，它使我考虑暴力。我只需要知道是否可能有这个过程自动化。我知道我可以为此编写代码，如果2010值为null，它需要被替换。任何帮助将是伟大的。

So, I have to update a database with 6000 lines of duplicates. The duplicates are companies who all share a common number code. My issue is I'm only replacing one or two fields at a time, ie company web site that was provided in 2009 but they didn't give it in 2010. This is all that needs to be replaced. I'm an access rookie and am doing all lines by hand, its making me contemplate violence. I just need to know if its possible to have this process automated. I know i could write code for this, if the 2010 value is null it needs to be replaced. Any help would be great. I'll also be around if you need some more info on the problem.

推荐答案

在注释中，您提供了这个示例数据（我创建了字段名称）：

In a comment, you supplied this sample data (I've created field names):

  CompanyName CompanyYear  Amount   Website         Contact
  starbucks   2009         100$     starbucks.com   matt bucky
  starbucks   2010         200$     null            null

因为你说公司名称匹配，类似这样：

Since you've said the company names match, something like this:

  UPDATE tblCompany INNER JOIN tblCompany AS C2 ON tblCompany.CompanyName = C2.CompanyName
  SET tblCompany.Website = C2.Website
  WHERE tblCompany.Website Is Null AND C2.Website Is Not Null
     AND tblCompany.CompanyYear=2010 AND C2.CompanyYear=2009;

这假设您要使用2009年记录的数据更新2010年记录，

This assumes that you want to update 2010 records with data from 2009 records, and that for each company there's only one record for each year.

要更新联系人姓名，您可以再次运行查询，适当地更改SET语句和WHERE子句：

To update the contact name, you'd run the query again, suitably altering the SET statement and WHERE clause:

  UPDATE tblCompany INNER JOIN tblCompany AS C2 ON tblCompany.CompanyName = C2.CompanyName
  SET tblCompany.Contact = C2.Contact
  WHERE tblCompany.Contact Is Null AND C2.Contact Is Not Null
     AND tblCompany.CompanyYear=2010 AND C2.CompanyYear=2009;

如果有较旧的记录可能有有用的数据，可以用子查询写一些复杂的SQL将一次性完成所有这一切，但我只是做同样的SQL的多个运行，首先比较2010年到2009年，然后到2008年，然后到2007年等。最终，将不会有任何更多的空记录。

If there are older records that might have useful data, it's possible to write some complicated SQL with subqueries that would do it all in one go, but I'd just do it with multiple runs of the same SQL, first comparing 2010 to 2009, then to 2008, then to 2007, etc. Eventually, there won't be any more Null records.

（这也似乎更像一个StackOverflow问题比一个ServerFault一个，但我知道什么？我永远不能弄清楚属于哪里）

(this also seems more like a StackOverflow question than a ServerFault one, but what do I know? I can never figure out what belongs where)

这篇关于Ms替换问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！