Postgresql Update with join

Question

I want to join 2 tables and update value of firts table on specified value of 2nd table. I failed using others solutions.

UPDATE customers
SET cutoffstop = cutoffstop + '432000'
FROM customers as c
JOIN bluemedia as b ON c.id = b.customerid
WHERE b.orderid = '217201807'

Solution

General Update Syntax:

[ WITH [ RECURSIVE ] with_query [, ...] ]
UPDATE [ ONLY ] table [ [ AS ] alias ]
    SET { column = { expression | DEFAULT } |
          ( column [, ...] ) = ( { expression | DEFAULT } [, ...] ) } [, ...]
    [ FROM from_list ]
    [ WHERE condition | WHERE CURRENT OF cursor_name ]
    [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]

Solution for you problem:

UPDATE customers AS c
SET cutoffstop = cutoffstop + 432000
FROM bluemedia as b
WHERE c.id = b.customerid
AND b.orderid = '217201807'

For more information on UPDATE syntax follow the below link:

https://www.postgresql.org/docs/current/static/sql-update.html

Comments

[rafal1137]

It gave me an error ERROR: syntax error at or near "AS" LINE 1: SELECT COUNT(*) AS total FROM (UPDATE customers AS cust ^

[Nishant Gupta]

Sorry I have assigned the wrong alias for customers. Now Try Again.

[rafal1137]

ERROR: syntax error at or near "SET" LINE 2: SET cutoffstop = cutoffstop + '432000' ^

[rafal1137]

It keeps throwing me an error ERROR: syntax error at or near "AS" LINE 1: SELECT COUNT(*) AS total FROM (UPDATE customers AS c ^

[wildplasser]

@rafal1137 There is no COUNT(*) in your original query.

[Nishant Gupta]

can you give me full query including SELECT Count(*) AS part

[Nishant Gupta]

@wildplasser Is there any syntax error in my query?

[rafal1137]

I think I found a reason why it was including count(*) with query I was using when inserting query using phpPgAdmin gui I had selected show in result in pages

[Anand Singh]

please let me know the data type of cutoffstop.. if cutoffstop is numeric or int then remove single code ....UPDATE customers SET cutoffstop = cutoffstop + 432000 FROM bluemedia as b ON customers.id = b.customerid WHERE b.orderid = '217201807'

[a_horse_with_no_name]

@rafal1137: you can't add strings using +. But if those are numbers you need to get rid of the useless single quotes around the number '432000' is a varchar, 432000 is an integer