Можно ли корректно заполнить создаваемое поле таблицы данными из другой таблицы?

Question

[CityzenUNDEAD]

Приветствую!
Имеются 2 таблицы (упрощено).
Таблица А

create table TableA
(
    Id serial PRIMARY KEY,
    Name CHARACTER varying(30),
    Number INTEGER
)

И таблица B

create table TableB
(
    Id serial PRIMARY KEY,
    Name CHARACTER varying(30),
    Desc CHARACTER(30)
)

TableB это по факту некоторый справочник имен. А в TableA в поле Name так же содержаться имена для каждой строки.
Все возможные значения поля Name из таблицы TableA, присутствуют в таблице справочнике имен TableB.
Задача в следующем: в таблицу TableA нужно добавить новое поле, внешний ключ TableBId который должен указывать на соответствующий Id в таблице TableB. Связь между таблицами должна установиться на основе поля Name. То есть должна быть проверка если
TableA.Name = TableB.Name, тогда TableA.TableBId = TableB.Id.
И когда все строки между этими двумя таблицами получат связи, поле TableA.Name можно удалять как не нужное.
Все это желательно сделать одним скриптом.
Но я пока не понимаю как это сделать, и реально ли это.

Comments

[Akina]

Все это желательно сделать одним скриптом.

Нерешаемо. Минимум два запроса - один создаст новое поле, второй заполнит его по указанному условию.

[CityzenUNDEAD]

Akina, ну хотя бы 2 тогда) Сложность составляет запрос на заполнение данных.

[mayton2019]

Что будет если ты сделаешь

insert into TableB select * from TableA;

?

Это будет решением твоей задачи?

[Akina]

CityzenUNDEAD,

Сложность составляет запрос на заполнение данных.

Да чё там сложного-то?

UPDATE tableA
SET tableA.tableB_Id = tableB.Id
FROM tableB
WHERE tableA.Name = tableB.Name

[CityzenUNDEAD]

Akina, у меня почему-то где выражение
SET tableA.tableB_Id = tableB.Id
Таблица tableB вообще не воспринимается. Всплывает сообщение "Unable to resolve symbol 'tableB "

[Akina]

CityzenUNDEAD, добавьте в вопрос INSERT INTO, по 2-3 записи на таблицу, и требуемый конечный результат для этих данных.

Answer 1

[Akina]

UPDATE TableA 
SET tableB_Id = TableB.Id
FROM TableB
WHERE TableA.Name = TableB.Name;

fiddle

Answer 2

[Adamos]

UPDATE TableA LEFT JOIN (
SELECT MIN(Id) AS id, Name FROM TableB GROUP BY Name
) AS b
ON TableA.Name = b.Name
SET TableA.TableBId = b.id

Соотвественно, до этого запроса - ALTER TABLE с добавлением столбца TableBId, после - ALTER TABLE с удалением Name.
Можно сделать "ногой в дверь", записывая значение прямо в Name и потом ALTER TABLE его с переименованием и сменой типа, но я бы не рисковал ;)

Comments

[Akina]

Можно сделать "ногой в дверь", записывая значение прямо в Name и потом ALTER TABLE его с переименованием и сменой типа, но я бы не рисковал

В данном случае изменяется VARCHAR(30) на BIGINT. Исходный тип гарантированно принимает любое валидное для конечного типа значение. Так что опасности в принципе не видать. Ну сбойнёт, откатится, можно повторить.

Главное, ненароком не запустить UPDATE дважды - вот тут и правда можно наиметь проблем, сразу и в полный рост.

=======

UPDATE TableA LEFT JOIN

Синтаксис хорош для MySQL - но у автора PostgreSQL. Поправить бы..

[Adamos]

Akina, так поправьте, я с Постгре не знаком.

[Akina]

Adamos, мне тоже придётся лезть в мануал за правильным синтаксисом. Я просто помню, что у Постгресса UPDATE .. SET .. FROM ..

Answer 3

[kalapanga]

Сначала создаёте новое поле. Следующим действием делаете Update таблицы TableA.
Про update читайте здесь: https://www.postgresql.org/docs/16/sql-update.html
Обратите внимание на вариант update from.
Результат не может быть полностью верным если в TableB поле Name неуникально, что по описанию таблицы возможно.