Transfer data from one table to another with both table having same id, in Postgres

I have 2 tables. TBL_A and TBL_B. I want to update TBL_A so that A_NAME = B_NAME, where A_LA = B_LA. How do I do this?
This is just s sample data. In the real table, I have thousands of records on each table.
I tried connecting both tables using View, but it seems that the view can’t be updated.

TBL_A:

CREATE TABLE public."TBL_A"
(
"A_LA" character varying,
"A_NAME" character varying,
"A_ID" bigint NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 1000 CACHE 1 ),
CONSTRAINT "TBL_A_pkey" PRIMARY KEY ("A_ID")
);

INSERT INTO public."TBL_A"("A_LA", "A_NAME") VALUES ('8904001', '');
INSERT INTO public."TBL_A"("A_LA", "A_NAME") VALUES ('8904003', '');
INSERT INTO public."TBL_A"("A_LA", "A_NAME") VALUES ('8904005', '');

TBL_B:

CREATE TABLE public."TBL_B"
(
"B_LA" character varying,
"B_NAME" character varying,
"B_ID" bigint NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 1000 CACHE 1 ),
CONSTRAINT "TBL_B_pkey" PRIMARY KEY ("B_ID")
);

INSERT INTO public."TBL_B"("B_LA", "B_NAME") VALUES ('8904001', 'John');
INSERT INTO public."TBL_B"("B_LA", "B_NAME") VALUES ('8904002', 'James');
INSERT INTO public."TBL_B"("B_LA", "B_NAME") VALUES ('8904003', 'Jacob')
INSERT INTO public."TBL_B"("B_LA", "B_NAME") VALUES ('8904004', 'Jared')
INSERT INTO public."TBL_B"("B_LA", "B_NAME") VALUES ('8904005', 'Josh');

View:

CREATE VIEW public."A_B_CONNECT_VIEW" AS
SELECT "TBL_A"."A_LA","TBL_A"."A_NAME","TBL_B"."B_LA","TBL_B"."B_NAME"
FROM "TBL_A" JOIN "TBL_B" ON "TBL_A"."A_LA"::text = "TBL_B"."B_LA"::text;

Answer

You can do it in this way:

UPDATE table_A AS A 
SET A.A_NAME = B.B_NAME
FROM table_B AS B
WHERE A.A_LA  = B.B_LA;

Advertisement

Answer