Динамический оператор UPDATE для обновления значений в столбцах, возвращаемых предыдущим SELECT
По сути, я хочу сделать следующее:
- найти все таблицы и их столбцы, которые соответствуют конкретному запросу,
- обновить значения в этих столбцах.
Так скажи у меня что то типа
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'
)
Или для 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')
Я хочу тогда сделать UPDATE на всех результирующих столбцах, аналогичных:
UPDATE _RESULTING_COLUMN_NAMES_HERE_THEORETICALLY_
SET
_SINGLE_COLUMN_NAME_ = _SOME_NEW_VALUE_
WHERE _SINGLE_COLUMN_NAME_ = _SOME_OLD_VALUE_;
Ну, очевидно, что это не работает или даже не существует, но я надеюсь, что вы понимаете, чего я хочу достичь.
Я мог видеть способ, где вы генерируете UPDATE заявление для каждой таблицы соответствия из SELECT набор результатов, но я не вижу, как этого добиться.
Чтобы сделать вещи более увлекательными, мне нужно сделать это для списка преобразований old_value в new_value.
Любые идеи приветствуются.
Я пытаюсь сделать эту работу на HSQLDB и Oracle как свои 2 требования, но поддержка дополнительных платформ была бы довольно хорошим бонусом.
2 ответа
Каждый раз, когда вы думаете, что вам нужно использовать динамический SQL, вы должны остановиться, сделать шаг назад и посмотреть, есть ли другой способ сделать это, или вам действительно нужно делать то, что вы делаете.
Я бы, наверное, серьезно усомнился в вашем базовом "требовании":
- обновление всех столбцов для всех таблиц, соответствующих некоторой строке, и типа integer (или их разновидности).
Что-то все еще пахнет "забавно"... Я бы ОЧЕНЬ осторожно относился к тому, что вы делаете - убедитесь, что вы знаете, какими будут результаты, тестовый тестовый тест... и снова ТЕСТ... на где-нибудь устройстве DEV...
Тем не менее, всякий раз, когда мне нужно прибегнуть к динамическому SQL, я обнаружил, что самый простой способ - начать с "шаблона":
Итак, в вашем случае последнее обновление, которое вы хотите запустить, выглядит так:
UPDATE _RESULTING_COLUMN_NAMES_HERE_THEORETICALLY_
SET
_SINGLE_COLUMN_NAME_ = _SOME_NEW_VALUE_
WHERE _SINGLE_COLUMN_NAME_ = _SOME_OLD_VALUE_;
Хорошо, я бы сейчас переписал это как строку и начал бы запрос, используя предложение 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
)
Обратите внимание, что я ничего не изменил в вашем запросе (пока). Все, что я сделал, это завернул немного "q'[" а также "]'" вокруг этого... rtrim, CHR(10) и положить его в WITH пункт.
1) q'[ some string ]' альтернативный способ сделать строку. Преимущество в том, что внутри строки можно заключать одинарные кавычки без каких-либо проблем: q'[ some 'string' ]' работает просто отлично... печатает " some 'string' "
2) RTRIM - Я оставил пробелы в конце строки как косметические, чтобы нам было легче читать. Однако, из-за ограничений длины строк, эти пробелы могут увеличивать эту строку очень быстро, очень быстро с большим запросом. Так RTRIM это привычка, в которую я попал. Оставьте косметические места, но скажите Oracle, чтобы они не использовались;) Они только для нас.
3) CHR(10) - только косметика - вы можете оставить это, если хотите. Мне нравится, что если вы хотите сбросить запрос во время тестирования, вы можете легко прочитать запрос и посмотреть, что он построил.
Далее мы изменим имена ваших динамических значений там, чтобы мы могли легче найти их и заменить их:
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
)
все, что я сделал, это создал легко идентифицируемые "строки", которые я буду использовать для подстановки значений позже.
Обратите внимание, что если ваши столбцы были строками, вам могут понадобиться кавычки: <col_name> = '<col_new_val>'но, кажется, вы имеете дело с целочисленными данными.. так что я думаю, что мы в порядке...
Теперь нам нужно получить ваши данные... поэтому мы вернемся к вашему исходному запросу:
SELECT COLUMN_NAME, TABLE_NAME
FROM USER_TAB_COLS
WHERE
(
COLUMN_NAME LIKE '%SOMETHING%'
OR COLUMN_NAME LIKE '%SOMETHINGELSE%'
) AND
DATA_TYPE IN ('NUMBER')
Хм, мне придется доверять вам ваш запрос там, я не уверен, что он будет работать на Oracle, но вы знаете свой запрос лучше, чем я;) Поэтому я буду доверять вашему запросу "как есть" для этого пример - до тех пор, пока он выбирает нужные вам данные и включает в себя имя таблицы, имя столбца и значения до / после, которые вы хотите (чего в данный момент нет), мы в порядке.
Так что все, что нам нужно сделать, это связать эти два вместе... мы сделаем это:
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')
)
Затем нам просто нужно добавить окончательный запрос, используя REPLACE для подстановки значений.
(примечание: не уверен, откуда вы получаете "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
Я ушел??? там для старых / новых значений, так как вы не указали, откуда они пришли?? но если вы запустите это, он должен выплевывать некоторые операторы обновления..;)
Как только вы освоитесь с ними, выполнить их сразу же - легкая работа.
Опять же, я бы посоветовал быть осторожным с этим подходом, это нормально для миграции 1 раз или около того, однако не рекомендуется, чтобы ежедневная работа выполнялась на регулярной основе.;)
найти все таблицы и их столбцы, соответствующие определенному запросу, обновить значения в этих столбцах.
С HSQLDB это невозможно сделать только с помощью SQL. Вам нужно написать короткую Java-программу для перечисления необходимых имен таблиц и их имен столбцов, затем создать оператор UPDATE для каждой таблицы и выполнить его.
С Oracle вы могли бы написать ту же программу на PL/SQL. Но решение языка Java совместимо с обоими механизмами баз данных.