0

Consider the following select query:

SELECT s.id, s.event, s.column, e.oldcolumn
FROM section as s
INNER JOIN event as e
ON s.event = e.id
WHERE s.id = 1

Now I wish to update the section table so that the values are equal to the oldcolumn from the event table.

I tried:

UPDATE public."section"
SET public."section".column = public."event".oldcolumn
FROM public."section"
INNER JOIN public."event"
on public."section".event = public."event".id
WHERE s.id = 1

However this, on postgresql, returned the following error:

ERROR:  table name "section" specified more than once
SQL state: 42712

How would I do such a thing, using joins inside an update query?

2
1

As documented in the manual you should not repeat the target table in the FROM clause.

So your UPDATE should be like this:

UPDATE public."section" s
  SET column = evt.oldcolumn
FROM public."event" evt
WHERE s.event = evt.id
  AND s.id = 1;
1

Disclaimer, I don't use PostgreSQL but I'm pretty familiar with SQL Server.

From what I understand in the PostgreSQL documentation here, I think the error you are getting is caused by the inclusion of public."section" INNER JOIN in your FROM statement. When using UPDATE you specify the table you are updating so adding public."section" in the FROM is not necessary. Per the documentation "Do not repeat the target table as a from_item unless you intend a self-join (in which case it must appear with an alias in the from_item)."

If you change your code to what I have below, I think it would resolve your issue. Though I'm not sure about the quotation marks you have, I just copied your format. The main difference being, only have the tables you're joining to in the FROM and move the ON to the WHERE.

UPDATE public."section"
SET public."section".column = public."event".oldcolumn
FROM public."event"
WHERE public."section".event = public."event".id AND public."section".id = 1
4
  • This won't work in Postgres May 22 '20 at 18:12
  • @a_horse_with_no_name I assume you were referring to the CTE syntax. I think I found what the issue was. Will it work in Postgres now?
    – MrPshaw
    May 22 '20 at 18:30
  • It will work, but it's way too complicated (and inefficient) May 22 '20 at 18:33
  • @a_horse_with_no_name Thanks. I removed the CTE suggestion. It's definitely helpful in SQL Server, but I agree I don't see the benefit in PostgreSQL.
    – MrPshaw
    May 22 '20 at 18:37
-2

This is a complete example, but check the update sentence at the end also check this example

DECLARE @section TABLE 
(
    id  INT PRIMARY KEY, 
    event int,
    [column] int
);

INSERT INTO @section(id, event,[column])
VALUES
    (1,3,77),
    (2,2,55),
    (3,4,88),
    (4,6,44),
    (5,1,33);

DECLARE @event TABLE
(
    id  INT PRIMARY KEY,     
    oldcolumn int
);

INSERT INTO @event(id, oldcolumn)
VALUES
    (1,177),
    (2,155),
    (3,188),
    (4,144),
    (5,133);

SELECT * FROM @section s

SELECT s.id, s.event, s.[column], e.oldcolumn
FROM @section s
INNER JOIN @event e ON s.event = e.id
WHERE s.id = 1

UPDATE s
SET s.[column] = e.oldcolumn
FROM @section s
INNER JOIN @event e ON s.event = e.id
WHERE s.id = 1

SELECT * FROM @section s

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.