CODEWITHSUNDEEP
postgresqlsql-update
Helloworld12
Helloworld12

Reputation: 15

Updating foreign key linked table in 1 step

How can I update (age and passwort) from tables with foreign key?

currently I'm trying:

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

edit: new try still doesnt work properly

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

says missing from clause, but with it it throws systax error

Table definitions:

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;

Upvotes: 0

Views: 55

Answers (1)

user330315
user330315

Reputation:

You need a FROM clause in the UPDATE:

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;

Upvotes: 1

Related Questions