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.
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', '');
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');
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";