SQL-запрос для заполнения ВСЕХ ЗНАЧЕНИЙ в ОДНОМ столбце
Это может показаться простым и часто задаваемым, но я удивлен, как часто на этот вопрос дают лишь частичный ответ.
Предположим, у меня есть база данных POSTGRESQL, в которой есть таблица со столбцом под названием «расчет» в таблице под названием «таблица». Этот столбец в настоящее время заполнен NULL.
У меня есть все значения для заполнения расчета вне базы данных. Представьте, что значения находятся в строке, разделенной запятыми. Теперь я должен построить оператор SQL, который изменяет ВСЕ значения «вычисления» в том порядке, в котором они появляются в такой строке.
Подобные вопросы здесь задавались, и в Интернете есть бесконечное количество руководств о том, как добавлять значения в базу данных, но они всегда предлагают:
UPDATE calculation FROM table SET calculation=value WHERE ...
Проблемы с этой командой заключаются в следующем: (1) для нее требуются условия WHERE, которых не существует (мы хотим обновить все значения, а не только значения, соответствующие определенному условию). (2) Предполагается, что для расчета используется только одно значение. Таким образом, кажется, что мы должны вызывать один запрос для каждой отдельной строки.
Люди, которые спрашивают, часто тяготеют к:
INSERT INTO table (calculation) VALUES ...
который идеально соответствует синтаксису, но не работает, потому что столбец уже есть, а строки уже созданы.
Это простая операция вне SQL. R, Python, Matlab и другие языки часто имеют возможность добавлять векторы в виде столбцов к их табличным эквивалентам. Или, что еще лучше, замените содержимое столбца вектором. Что мне не хватает?
Обновлено: БОЛЕЕ КОНКРЕТНЫЙ ПРИМЕР:
Предположим, что следующая таблица уже существует в базе данных db с именем «таблица».
Я хочу заполнить поле расчета значениями "akdak", "dndja" и "jsnajsna" соответственно. Такой, что:
Как я могу сделать это в одном SQL-запросе без ссылки на значения PKID и имени в предложении WHERE?
Краткий ответ: сделайте обновление без WHERE. Длинный ответ: вы должны добавить детали к своему вопросу, чтобы на него ответили. В SQL нет смысла говорить о порядке записей без указания столбцов, используемых для их сортировки. Вы можете полагаться только на логическое упорядочение своих запросов, а не на то, как они физически хранятся (что может измениться без вашего контроля). И это без упоминания, что строка, разделенная запятыми, может также не соответствовать количеству записей в вашей таблице. Даже если вы проверите соответствие перед обновлением, оно может быть уже изменено другим пользователем, когда вы продолжите.
Я думаю, что ваше утверждение о SQL является ответом. Но я приведу более полный пример, для ясности
Пример добавлен по запросу
Это не сохраняет значение во всех строках, оно вычисляет значение по какой-то неизвестной формуле. Откуда берутся эти цифры? Это номер строки, основанный на каком-то порядке? Копия ПК? Что-то другое? Почему бы не использовать UPDATE calulation FROM table SET calculation=PKID?
Извините, я, должно быть, что-то упустил в своем объяснении. Предположим, что в этой таблице ТОЛЬКО 3 строки. Чтобы избавиться от каких-либо сложностей с вычислениями, предположим, что вычисление было NULL, и я проснулся сегодня и решил заполнить его числами 1,2 и 3. Как это сделать?
Чтобы сделать проблему более реальной. У меня действительно сложный скрипт, который выводит вектор символов (представление объекта языка R). Этот сценарий принимает в качестве входных данных имена в имени столбца. Однако ради скорости скрипта имеет смысл загружать все имена с помощью SELECT name FROM table , запускать мой скрипт и получать список значений для вставки/выгрузки в расчете. Подобно тому, как я объединил входные данные в одном запросе, имеет смысл вставлять/загружать выходные данные в один запрос.
Не просите людей предположить или привести примеры того, как вы решили бы проблему на другом языке. В чем проблема в первую очередь? Отредактируйте вопрос и объясните, откуда берутся эти цифры. Например, вы можете рассчитать номера строк, ранги, процентили с помощью функций ранжирования, таких как ROW_NUMBER() OVER(ORDER BY SomeField). SQL-запросы — это не сценарии, это описание логики, основанной на наборах. Сервер должен разработать эффективную стратегию выполнения. Если у вас есть индексы, запрос будет быстрым. Если нет, то будет медленно.
SQL имеет дело с множествами, а не с векторами. В наборе нет порядка строк, даже физического порядка хранения. Если сервер использует параллельное выполнение в запросе, результаты могут поступать в любом порядке. Если вы хотите хранить определенные значения в определенных записях, вы должны указать первичный ключ.
Понятно, поэтому ответ "нельзя". Но только в адрес вашего предыдущего комментария. Я прошу людей предположить, потому что расчет не актуален. Я сожалею даже об использовании этого термина сейчас, так как он явно запутал вопрос. Минимальный воспроизводимый пример такой, как указано. У меня есть набор значений, например, «акдак», «днджа», «джснайсна». Неважно их происхождение. Вывод таблицы SELECT * FROM такой, как указано в первой таблице. Я с помощью одной команды UPDATE установил значение строки 1 как «akdak», значение строки 2 как «dndja» и значение строки 3 «jsnajsna».
Итак, первое значение в алфавитном порядке в столбце name (alpha) получает первое значение, также в алфавитном порядке, из вашего списка значений (akdak)? Второе значение в name получает второе значение в вашем списке и так далее?
Да, спасибо! Точно! Чтобы было еще понятнее, первое значение имени (alpha) получает первое значение списка (akdak), второе значение имени (beta) получает второе значение списка dndja, третье значение имени (theta ) получает третье значение jsnajsna.
Для еще большего акцента теперь сообщил Панайотис. Алфавитный порядок работает, но то же самое можно сказать и о числовом порядке PKID. Важной концепцией является то, как ОБНОВИТЬ весь столбец за один вызов, не используя разные предложения WHERE для каждой строки.
@ JMenezes нет, ответ what is the question and why don't you use the PKs? Даже если вы хотите отправить данные в виде пакета, использование значений PK намного проще, безопаснее и быстрее, чем пытаться угадать, какие записи нужно обновить.
so would the numerical order of PKID вам не нужно заказывать идентификаторы. Вы можете использовать их для однозначного соединения строк результатов со строками таблицы. Нет причин размещать несколько идентификаторов в WHERE
Ответы 2
Вы хотите выполнить что-то, что использует оконную функцию ROW_NUMBER() для создания соответствия между записями на основе порядка:
WITH ValueList(Calculation) AS (
Values('akdak'),('dndja'),('jsnajna')
)
UPDATE MyTable
SET Calculation = V.Calculation
FROM (
SELECT Name, ROW_NUMBER() OVER (ORDER BY Name)
FROM MyTable
) T(Name, rownum)
JOIN (
SELECT Calculation, ROW_NUMBER() OVER (ORDER BY Calculation)
FROM ValueList
) V(Calculation, rownum)
ON T.rownum = V.rownum
WHERE MyTable.Name = T.Name
JOIN сделает так, чтобы количество значений не совпадало с записями в таблице. Дополнительные значения будут игнорироваться, столбец calculation дополнительной записи останется нулевым.
Примечание. Я поставил WHERE MyTable.Name = T.Name в конце запроса, но в зависимости от того, какие столбцы являются UNIQUE и/или NOT NULL и какое именно поведение вы хотите, вы можете предпочесть использовать WHERE MyTable.PKID = T.PKID.
Большое спасибо. Я обновлю вопрос, чтобы значения не были 1,2,3 и соответствовали ответу для потомков!
Почему бы не использовать значения первичного ключа вместо того, чтобы пытаться создать их заново? Этот UPDATE работал бы лучше и быстрее, если бы конструктор VALUES включал значения PK, а не ждал, пока ROW_NUMBER создаст их. Кроме того, ничто не гарантирует, что данные были загружены по заказу Name
Суть, которую я подчеркивал в своих комментариях прямо под вопросом, заключалась в том, чтобы сортировать записи в логическом порядке, то есть на основе значений в столбце. По своей природе первичный ключ используется только для уникальной ссылки на каждую запись; ему не следует придавать значения. С учетом сказанного, использование PKID для искусственного создания заказа также не является полной ерундой, поэтому я добавил об этом примечание.
Что касается порядка загрузки данных в таблицу, то он недоступен. Лучшее, что вы можете использовать, это записи заказов, в которых физически хранятся записи, которые могут быть одинаковыми, но также могут быть и разными (например, с оптом INSERT, DELETE, VACUUM, INSERT). Вы можете использовать столбец inserted_ts для хранения метки времени каждой вставки в таблицу, но его использование вернет вас к логическому порядку (поскольку это столбец в таблице).
Основываясь на принятом ответе, кажется, что реальный вопрос заключается в том, как выполнить пакетное обновление с помощью одного запроса.
Для большого количества данных (тысячи и более) типичным решением является вставка данных в промежуточную таблицу, например, с помощью COPY, а затем обновление целевой таблицы путем ПРИСОЕДИНЕНИЯ к значениям идентификатора. Поскольку первичные ключи индексируются, JOIN будет быстрым:
UPDATE Target
Set
Calculation1=staging.Calculation1,
...
FROM Source INNER JOIN Target
ON Source.ID=Target.ID
Для меньшего количества строк можно использовать конструктор табличных значений для выдачи идентификатора и новых значений:
UPDATE Target
Set
Calculation1=staging.Calculation1,
...
FROM (
VALUES
(1, 'London'),
(2, 'Rio de Janeiro'),
(3, 'Tokyo')
) as Source(ID, Field1)
INNER JOIN Target
ON Source.ID=Target.ID;
вам не хватает данных образца и желаемого результата