BigQuery存储过程-将UPDATE语句中的变量用于表名 [英] BigQuery Stored Procedure - Use variable in UPDATE statement for table name

问题描述

我正在尝试在存储过程中使用while循环来更新表列表.尝试执行以下代码时，出现错误:表名"table_name"；缺少数据集，而请求中未设置默认数据集.似乎在 UPDATE 之后，存储过程未正确读取变量 table_name .故意不支持吗?

I am trying to use a while loop in a stored procedure to update a list of tables. When trying to execute the below code, I am getting the error: Table name "table_name" missing dataset while no default dataset is set in the request. It seems the stored procedure is not correctly reading the variable table_name when it is after UPDATE. Is this intentionally not supported?

DECLARE table_names ARRAY<STRING>;
DECLARE table_name STRING;
DECLARE INDEX INT64 DEFAULT 0;

SET table_names = [
  "`dev.table`",
  "`dev.table2`"
];

BEGIN
  WHILE INDEX < 2 DO
    SET table_name = table_names[OFFSET(INDEX)];
    SELECT table_name;
    UPDATE table_name
    SET name = "new_name"
    WHERE name = "old_name";
    SET INDEX = INDEX + 1;
  END WHILE;
END

推荐答案

在下面的脚本片段中

UPDATE table_name
SET name = "new_name"
WHERE name = "old_name";  

您应该使用立即执行-像下面一样

you should use EXECUTE IMMEDIATE - something like below

EXECUTE IMMEDIATE '''
UPDATE ''' || table_name || '''
SET name = "new_name"
WHERE name = "old_name"''';  

这篇关于BigQuery存储过程-将UPDATE语句中的变量用于表名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！