通过 1 步更新外键链接表

Question

如何使用外键从表更新(年龄和密码(？

目前我正在尝试：

WITH uid AS (
UPDATE benutzer
SET alter = 99
WHERE name = 'sbUVefJtnRZ'
RETURNING userid)
UPDATE passwoerter
SET (Passwort) userid, 'bars' from uid;

编辑：新尝试 仍然无法正常工作

WITH uid AS (
UPDATE benutzer
SET alter = 99
WHERE name = 'sbUVefJtnRZ'
RETURNING userid)
UPDATE passwoerter
SET passwort = 'bars'
WHERE benutzer_userid = benutzer.userid;

说缺少子句，但它抛出了系统税错误

表定义：

CREATE TABLE public.benutzer (
"userid" serial NOT NULL,
name character varying(30) NOT NULL,
alter integer,
email character varying(120),
telefonnr character varying(30),
agb boolean,
datenschutz boolean,
PRIMARY KEY ("userid")
);

ALTER TABLE public.benutzer
OWNER to postgres;
CREATE TABLE public.passwoerter
(
"userid" integer NOT NULL,
passwort character varying(60),
CONSTRAINT passwoerter_pkey PRIMARY KEY (benutzer_userid),
CONSTRAINT passwoerter_fkey FOREIGN KEY (benutzer_userid)
REFERENCES public.benutzer (userid) MATCH SIMPLE
ON UPDATE NO ACTION
ON DELETE CASCADE
NOT VALID
);
ALTER TABLE public.passwoerter
OWNER to postgres;

Answer 1

在UPDATE 中需要一个 FROM 子句：

WITH uid AS (
UPDATE benutzer
SET alter = 99
WHERE name = 'sbUVefJtnRZ'
RETURNING userid
)
UPDATE passwoerter
SET passwort = 'bars'
FROM uid 
WHERE uid.userid = passwoerter.userid;