CODEWITHSUNDEEP
sqlpostgresqlplpgsqlstored-functions
JOE
JOE

Reputation: 3

Create function to update two tables in SQL

I have two table, with different columns, and I have to create a function to update the second table with the information from the first when the columns year match with the given year and group all in gender groups.

Here I put my code but I don't know if it is okay because the pgAdmin4 returns me an error because of the $$ symbols, although I am trying to solve it.

BEGIN WORK;

CREATE OR REPLACE FUNCTION update_report(p_year INTEGER) 
RETURNS SETOF report1 AS $$
DECLARE report report1;
BEGIN
UPDATE report1 SET m.num=t_num, m.na=t_na, m.nd=t_nd, m.birth=t_birth
FROM report2 m
    FULL JOIN report1
    ON p_year= m.year
GROUP BY m.gender);

   --In case there are any film in the specified year
IF NOT FOUND THEN
    RAISE EXCEPTION  ‘’%: No film exists in this year’’, -746;
END IF;
RETURN report;

END;
$$ LANGUAGE plpgsql;

COMMIT WORK;

Can someone just tell if it is okay? Thanks

Upvotes: 0

Views: 86

Answers (1)

user330315
user330315

Reputation:

I am not sure what "group by gender" is supposed to mean, but I think you are looking for a simple UPDATE.

Note that you should not repeat the target table in the FROM clause of an UPDATE statement.

Quote from the manual

Note that the target table must not appear in the from_list, unless you intend a self-join

You didn't show us any sample data and expected results or the structure of the tables, so I can only guess, what exactly you want, but something like this maybe:

UPDATE report1 r1
   SET num = r2.t_num, 
       na = r2.t_na, 
       nd = r2.t_nd
       birth = r.t_birth
FROM report2 r2
WHERE r1.year = r2.year
  AND r1.gender = r2.gender
  AND r1.year = p_year= m.year;

If you want to return all rows that have been changed, then you can use the RETURNING clause. You don't need a declare for that. Using language sql instead of PL/pgSQL makes this even simpler:

CREATE OR REPLACE FUNCTION update_report(p_year INTEGER) 
   RETURNS SETOF report1 
AS $$
  UPDATE report1 r1
     SET num = r2.t_num, 
         na = r2.t_na, 
         nd = r2.t_nd
         birth = r.t_birth
  FROM report2 r2
  WHERE r1.year = r2.year
    AND r1.gender = r2.gender
    AND r1.year = p_year= m.year
  RETURNING r1.*; --<< return all modified rows
$$ 
LANGUAGE sql;

Upvotes: 1

Related Questions