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

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'

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

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Related