Home > Software design >  Transfer data from one table to another with both table having same id, in Postgres
Transfer data from one table to another with both table having same id, in Postgres

Time:11-14

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:
enter image description here

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:
enter image description here

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:
enter image description here

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;

CodePudding user response:

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; 

CodePudding user response:

I update the query from @Farshid Shekari for it to work.

UPDATE "TBL_A" AS A 
SET "A_NAME" = B."B_NAME"
FROM "TBL_B" AS B
WHERE A."A_LA"  = B."B_LA";
  • Related