Postgresql Update & Inner Join

Question

I am trying to update data in Table: local.import_payments from Table: local.payments based on update and Inner Join queries. The query I used:

Update local.import_payments
  Set local.import_payments.client_id = local.payments.payment_for_client__record_id,
local.import_payments.client_name = local.payments.payment_for_client__company_name,
local.import_payments.customer_id = local.payments.customer__record_id,
local.import_payments.customer_name = local.payment_from_customer,
local.import_payments.payment_id = local.payments.payment_id
From local.import_payments
Inner Join local.payments 
Where local.payments.copy_to_imported_payments = 'true'

The client_id, client_name, customer_id, customer_name in the local.import_payments need to get updated with the values from the table local.payments based on the condition that the field copy_to_imported_payments is checked.

I am getting a syntax error while executing the query. I tried a couple of things, but they did not work. Can anyone look over the queries and let me know where the issue is

Geogie · Accepted Answer · 2017-07-27 20:26:06Z

2

Try the following

UPDATE local.import_payments 
Set local.import_payments.client_id = 
local.payments.payment_for_client__record_id,
local.import_payments.client_name = 
local.payments.payment_for_client__company_name,
local.import_payments.customer_id = local.payments.customer__record_id,
local.import_payments.customer_name = local.payment_from_customer,
local.import_payments.payment_id = local.payments.payment_id
FROM  local.payments as lpay
WHERE lpay.<<field>> = local.import_payments.<<field>>
AND local.payments.copy_to_imported_payments = 'true'

answered Jul 27, 2017 at 20:26

Geogie

313 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

eden Over a year ago

Please explain your answer in detail instead of asking the questioner to try your solution.

Abelisto · Accepted Answer · 2017-07-27 21:37:59Z

You shouldn't to specify the schema/table for updated columns, only column names:

Do not include the table's name in the specification of a target column — for example, UPDATE table_name SET table_name.col = 1 is invalid.

from the doc

You shouldn't to use the updating table in the from clause except of the case of self-join.

You can to make your query shorter using "column-list syntax".

update local.import_payments as target
  set (
    client_id,
    client_name,
    customer_id,
    customer_name,
    payment_id) = (
    source.payment_for_client__record_id,
    source.payment_for_client__company_name,
    source.customer__record_id,
    source.payment_from_customer,
    source.payment_id)
from local.payments as source
where
  <join condition> and
  source.copy_to_imported_payments = 'true'

Collectives™ on Stack Overflow

Postgresql Update & Inner Join

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related