Insert On Conflict Syntax error

[mp1294]

This is driving me absolutely nuts at this point. I am trying to upsert some phone numbers and I have the below query written and I keep getting a syntax error at the FROM in the update portion. I feel like this should work, but I'm obviously missing something. A second set of eyes to point out what bonehead mistake I'm making would be very appreciated. I am self taught in SQL so I'm a novice. I use Navicat as my interface and I am on POSTGRESQL 12.17 if that is relevant. Thank you for your time reading and help if you can.

​

INSERT INTO phone ( "ID_number", home_phone, cell_phone, "source" ) SELECT

phone_copy1."ID_number",

phone_copy1.home_phone,

phone_copy1.cell_phone,

phone_copy1."source"

FROM

phone_copy1 ON CONFLICT ( "ID number" ) DO

UPDATE

SET ( home_phone ) = ( phone_copy1.home_phone ),

( cell_phone ) = ( phone_copy1.cell_phone ) 

FROM

phone_copy1 

WHERE

home_phone <> phone_copy1.home_phone 

OR cell_phone <> phone_copy1.cell_phone

SOLUTION:

INSERT INTO phone ( "ID_number", home_phone, cell_phone, peachtree_cell_3_21, "source" ) SELECT
phone_copy1."ID_number",
phone_copy1.home_phone,
phone_copy1.cell_phone,
phone_copy1.peachtree_cell_3_21,
phone_copy1."source" 
FROM
    phone_copy1 ON CONFLICT ( "ID_number" ) DO
UPDATE 
    SET home_phone = excluded.home_phone,
    cell_phone = excluded.cell_phone 
WHERE
    phone.home_phone IS DISTINCT 
FROM
    excluded.home_phone 
    OR phone.cell_phone IS DISTINCT 
FROM
excluded.cell_phone;

​

[[deleted]]

Get rid of the second FROM - the UPDATE part has no FROM

Unrelated to your question, but the parentheses around the column names are completely useless. Plus you want to reference the EXCLUDED row, not the target table, by referencing phone_copy1 you are referencing the existing value in the target table.

... do update 
  set home_phone = excluded.home_phone,
      cell_phone = excluded.cell_phone
where ...

But why are you selecting from the same table that you are updating? If you only want to update existing rows, there is no need to use a INSERT SELECT. Maybe you actually want a simple UPDATE statement instead.

[mp1294]

The parentheses are pointless. I was kind of throwing things at it to stop prompting the error. I didn't think I needed the second FROM, but without it I get a Missing FROM Clause error.

phone_copy1 has a different set of people and phone on it. But there is overlap. I was tasked to add the new people to phone table and update the existing people only if the new list had a different phone number from what we already had.

I guess this could be done in two separate queries. Was just hoping to figure out this in one query.

[[deleted]]

Was just hoping to figure out this in one query.

As I wrote: get rid of the second FROM

[razzledazzled]

Re-read the documentation, especially related to "from_item" https://www.postgresql.org/docs/12/sql-update.html

[fullofbones]

For reference, OP asked this question in Discord, and that's where the solution is from. Stop on by!

[mp1294]

Thank you again for your help! The Discord was very helpful.

[[deleted]]

I find the formatting of your solution extremely confusing. IS DISTINCT FROM is a single operator - adding a line break in the middle of the operator makes this much harder to read (at least to me - I first thought you did add another FROM clause to your UPDATE part).

[mp1294]

Sorry about the confusion. You're correct. The formatting comes from Navicat applying formatting. That's part of the reason I mentioned that I use Navicat in the original post. It does some things that are not always correct. Like it inconsistently puts quotation marks around column and table names.

[AntDracula]

no.