动态UPDATE语句，用于更新上一个SELECT返回的列中的值

In essence, what I want to do is:

从本质上讲，我想做的是：

• find all tables and their columns that match a specific query,
• 找到与特定查询匹配的所有表及其列，
• update values in these columns.
• 更新这些列中的值。

So say I have something like

所以说我有类似的东西

SELECT COLUMN_NAME, TABLE_NAME, TABLE_SCHEMA
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE
    (
      TABLE_SCHEMA = 'PUBLIC'
    ) AND (
      COLUMN_NAME LIKE '%SOMETHING%'
      OR COLUMN_NAME LIKE '%SOMETHINGELSE%'
    ) AND (
      DATA_TYPE = 'BIGINT' OR
      DATA_TYPE = 'TINYINT' OR
      DATA_TYPE = 'SMALLINT' OR
      DATA_TYPE = 'INTEGER'
    )

Or for Oracle something like:

或者对于Oracle来说：

SELECT COLUMN_NAME, TABLE_NAME
  FROM USER_TAB_COLS
  WHERE
    (
      COLUMN_NAME LIKE '%SOMETHING%'
      OR COLUMN_NAME LIKE '%SOMETHINGELSE%'
    ) AND
    DATA_TYPE IN ('NUMBER')

I want to then do an UPDATE on all resulting columns similar to:

我想对所有结果列执行UPDATE类似于：

UPDATE _RESULTING_COLUMN_NAMES_HERE_THEORETICALLY_
  SET
    _SINGLE_COLUMN_NAME_ = _SOME_NEW_VALUE_
  WHERE _SINGLE_COLUMN_NAME_ = _SOME_OLD_VALUE_;

Well obviously that does not work or even exist, but I hope you understand what I want to achieve.

很明显，这不起作用甚至不存在，但我希望你明白我想要实现的目标。

I could see a way where you generate an UPDATE statement for each matching table from the SELECT resultset, but I don't really see how to achieve this.

我可以看到一种方法，你可以从SELECT结果集为每个匹配的表生成一个UPDATE语句，但我真的没有看到如何实现这一点。

To make things more fun, I'd need to do that for a list of old_value to new_value transformations.

为了让事情更有趣，我需要为old_value到new_value转换的列表这样做。

Any ideas are welcome.

欢迎任何想法。

I am trying to have this work on HSQLDB and Oracle as my 2 requirements, but supporting additional platforms would be a pretty good bonus.

我试图将这个工作放在HSQLDB和Oracle上作为我的2个要求，但支持其他平台将是一个相当不错的奖励。

2 个解决方案

#1

Anytime you think you need to use dynamic SQL, you should stop, take a step back and see if there's another way to do it, or if you REALLY need to do what you're doing .

任何时候你认为你需要使用动态SQL，你应该停下来，退后一步，看看是否有另一种方法，或者你真的需要做你正在做的事情。

I'd probably seriously question your base "requirement" of:

我可能会严重质疑你的基本“要求”：

• updating all columns for all tables matching some string, and of type integer (or variations thereof).
• 更新匹配某些字符串的所有表的所有列，以及整数类型（或其变体）。

Something still smells "funny" ... I'd be VERY careful about what you're doing - make sure you know what the results are going to be, test test test .. and TEST again ... on a DEV box somewhere ...

有些东西仍然闻起来“有趣”......我会非常小心你正在做什么 - 确保你知道结果会是什么，测试测试...再次测试......在某个DEV盒子上...

that said, anytime I need to resort to dynamic SQL, I have found the simplest way is to start with a "template":

那说，任何时候我需要求助于动态SQL，我发现最简单的方法是从“模板”开始：

So in your case, the final UPDATE you want to fire is as you put it:

所以在你的情况下，你想要触发的最终更新是你所说的：

  UPDATE _RESULTING_COLUMN_NAMES_HERE_THEORETICALLY_
    SET
      _SINGLE_COLUMN_NAME_ = _SOME_NEW_VALUE_
    WHERE _SINGLE_COLUMN_NAME_ = _SOME_OLD_VALUE_;

Ok, I'd probably re-write that as a string now, and start a query using the WITH clause:

好的，我现在可能会把它重写为字符串，并使用WITH子句启动查询：

  WITH w_template AS ( select 
        rtrim(q'[ UPDATE _RESULTING_COLUMN_NAMES_HERE_THEORETICALLY_    ]')||CHR(10)||
        rtrim(q'[   SET                                                 ]')||CHR(10)||
        rtrim(q'[     _SINGLE_COLUMN_NAME_ = _SOME_NEW_VALUE_           ]')||CHR(10)||
        rtrim(q'[   WHERE _SINGLE_COLUMN_NAME_ = _SOME_OLD_VALUE_;      ]')
           template from dual
        )

Note I haven't changed anything in your query (yet). All I did was wrap some "q'[" and "]'" around it ... an rtrim, a CHR(10) and put it in a WITH clause.

注意我还没有改变你的查询中的任何内容。我所做的就是在它周围包裹一些“q”[“和”]“......一个rtrim，一个CHR（10）并把它放在一个WITH子句中。

1) q'[ some string ]' is an alternate way to do a string. The advantage of it is you can have single quotes inside that string without any real issue: ie q'[ some 'string' ]' works just fine ... prints " some 'string' "

1）q'[some string]'是执行字符串的另一种方法。它的优点是你可以在该字符串中使用单引号而没有任何实际问题：即q'[some'string']'工作得很好...打印“some'字符串'”

2) RTRIM - I left spaces at end of line in there as cosmetic so it's easier for us to read. However, due to length restrictions of strings, those spaces can grow that string really big, really fast with a larger query. So RTRIM is a habit I've gotten into . Keep the cosmetic spaces, but tell Oracle not to use them ;) they're just for us.

2）RTRIM - 我在那里留下了作为装饰的行尾的空格，所以我们更容易阅读。但是，由于字符串的长度限制，这些空格可以增长，字符串非常大，非常快，查询量更大。所以RTRIM是我养成的习惯。保留化妆品空间，但告诉Oracle不要使用它们;）它们只适合我们。

3) CHR(10) - cosmetic only - you can leave this off if you want. I like it as if you want to dump the query during testing, you can easily read the query and see what it built.

3）CHR（10） - 仅限化妆品 - 如果需要，可以将其关闭。我喜欢它，就像你想在测试期间转储查询一样，你可以轻松阅读查询并查看它的构建内容。

Next we'll change the names of your dynamic values there so we can more easily spot them and substitue them:

接下来我们将更改动态值的名称，以便我们更容易发现它们并替换它们：

  WITH w_template AS ( select 
        rtrim(q'[ UPDATE <table_name>                   ]')||CHR(10)||
        rtrim(q'[   SET                                 ]')||CHR(10)||
        rtrim(q'[     <col_name> = <col_new_val>        ]')||CHR(10)||
        rtrim(q'[   WHERE <col_name> = <col_old_val>;   ]')
           template from dual
        )

all I did was create an easily identified "strings" that I'll use to substitute values in later.

我所做的只是创建一个容易识别的“字符串”，我将用它来替换后面的值。

Note that if your columns were strings, you might need quotes in there: <col_name> = '<col_new_val>' but seems you're dealing with integer data .. so I think we're ok ...

请注意，如果您的列是字符串，那么您可能需要引号： =' '但似乎您正在处理整数数据..所以我认为我们没问题......

Now we need to pull your data ... so we go back to your original query:

现在我们需要提取您的数据......所以我们回到原始查询：

        SELECT COLUMN_NAME, TABLE_NAME
          FROM USER_TAB_COLS
          WHERE
            (
              COLUMN_NAME LIKE '%SOMETHING%'
              OR COLUMN_NAME LIKE '%SOMETHINGELSE%'
            ) AND
            DATA_TYPE IN ('NUMBER')

Hmm, I'll have to trust you in your query there, I'm not sure that'll run on Oracle, but you know your query better than I do ;) So I'll trust your query "as is" for this example - as long as it picks out the data you want, and includes the table name, column name, and the before/after values you want (which it currently doesn't) we're ok.

嗯，我将不得不相信你的查询，我不确定是否会在Oracle上运行，但你比我更了解你的查询;）所以我会相信你的查询“就是这样”例如 - 只要它挑选出你想要的数据，并包括你想要的表名，列名和前/后值（它当前没有），我们就可以了。

So all we need to do is tack those two together ... we'll do this:

所以我们需要做的就是将这两者结合在一起......我们将这样做：

  WITH w_template AS ( select 
        rtrim(q'[ UPDATE <table_name>                   ]')||CHR(10)||
        rtrim(q'[   SET                                 ]')||CHR(10)||
        rtrim(q'[     <col_name> = <col_new_val>        ]')||CHR(10)||
        rtrim(q'[   WHERE <col_name> = <col_old_val>;   ]')
           template from dual
        )
     w_data AS (
        SELECT COLUMN_NAME, TABLE_NAME
          FROM USER_TAB_COLS
          WHERE
            (
              COLUMN_NAME LIKE '%SOMETHING%'
              OR COLUMN_NAME LIKE '%SOMETHINGELSE%'
            ) AND
            DATA_TYPE IN ('NUMBER')
        )

Then we just need to add the final query, using REPLACE to substitute values ..

然后我们只需要添加最终查询，使用REPLACE替换值。

(note: not sure where you get "some_new_value" and "some_old_value" from ??? you'll have to join that into your query .. )

（注意：不知道你从哪里得到“some_new_value”和“some_old_value”???你必须将它加入你的查询..）

  WITH w_template AS ( select 
        rtrim(q'[ UPDATE <table_name>                   ]')||CHR(10)||
        rtrim(q'[   SET                                 ]')||CHR(10)||
        rtrim(q'[     <col_name> = <col_new_val>        ]')||CHR(10)||
        rtrim(q'[   WHERE <col_name> = <col_old_val>;   ]')
           template from dual
        ),
     w_data AS (
        SELECT COLUMN_NAME, TABLE_NAME
          FROM USER_TAB_COLS
          WHERE
            (
              COLUMN_NAME LIKE '%SOMETHING%'
              OR COLUMN_NAME LIKE '%SOMETHINGELSE%'
            ) AND
            DATA_TYPE IN ('NUMBER')
        )
  SELECT REPLACE ( REPLACE ( REPLACE ( REPLACE ( 
              wt.template, '<table_name>', 
                             wd.table_name ),
                 '<col_name>', wd.column_name ),
                 '<col_new_val>', ??? ),
                 '<col_old_val>', ??? )  query
    FROM w_template wt,
         w_data wd

I left ??? there for the old / new values, since you didn't indicate where they'd come from ?? but if you run that, it should spit out some update statements .. ;)

我离开了 ？？？有旧的/新的价值观，因为你没有说明他们来自哪里？但如果你运行它，它应该吐出一些更新语句..;）

Once you're comfortable with those, pushing them through execute immediate is the easy work.

一旦你对这些感到满意，推动他们立即执行是一项简单的工作。

Again, I would advise to be cautious of this approach, this is ok for a 1 off migration, or such, however, it is not advised for a daily job to be running on a regular basis. ;)

同样，我建议对这种方法保持谨慎，这对于1次迁移是可以的，或者这样，但是，不建议每天的工作定期运行。 ;）

#2

find all tables and their columns that match a specific query, update values in these columns.

查找与特定查询匹配的所有表及其列，更新这些列中的值。

With HSQLDB, it is not possible to do this with just SQL. You need to write a short Java program to list the required table names and their column names, then construct an UPDATE statement per table and execute it.

使用HSQLDB时，不可能仅使用SQL执行此操作。您需要编写一个简短的Java程序来列出所需的表名及其列名，然后为每个表构造一个UPDATE语句并执行它。

With Oracle, you could write the same program in PL/SQL. But the Java language solution is compatible with both database engines.

使用Oracle，您可以在PL / SQL中编写相同的程序。但Java语言解决方案与两个数据库引擎兼容。