{
    "mode": "man",
    "parameter": "UPDATE",
    "section": "7",
    "url": "https://www.chedong.com/phpMan.php/man/UPDATE/7/json",
    "generated": "2026-06-15T13:38:58Z",
    "synopsis": "[ WITH [ RECURSIVE ] withquery [, ...] ]\nUPDATE [ ONLY ] tablename [ * ] [ [ AS ] alias ]\nSET { columnname = { expression | DEFAULT } |\n( columnname [, ...] ) = [ ROW ] ( { expression | DEFAULT } [, ...] ) |\n( columnname [, ...] ) = ( sub-SELECT )\n} [, ...]\n[ FROM fromitem [, ...] ]\n[ WHERE condition | WHERE CURRENT OF cursorname ]\n[ RETURNING { * | outputexpression [ [ AS ] outputname ] } [, ...] ]",
    "sections": {
        "NAME": {
            "content": "UPDATE - update rows of a table\n",
            "subsections": []
        },
        "SYNOPSIS": {
            "content": "[ WITH [ RECURSIVE ] withquery [, ...] ]\nUPDATE [ ONLY ] tablename [ * ] [ [ AS ] alias ]\nSET { columnname = { expression | DEFAULT } |\n( columnname [, ...] ) = [ ROW ] ( { expression | DEFAULT } [, ...] ) |\n( columnname [, ...] ) = ( sub-SELECT )\n} [, ...]\n[ FROM fromitem [, ...] ]\n[ WHERE condition | WHERE CURRENT OF cursorname ]\n[ RETURNING { * | outputexpression [ [ AS ] outputname ] } [, ...] ]\n",
            "subsections": []
        },
        "DESCRIPTION": {
            "content": "UPDATE changes the values of the specified columns in all rows that satisfy the condition.\nOnly the columns to be modified need be mentioned in the SET clause; columns not explicitly\nmodified retain their previous values.\n\nThere are two ways to modify a table using information contained in other tables in the\ndatabase: using sub-selects, or specifying additional tables in the FROM clause. Which\ntechnique is more appropriate depends on the specific circumstances.\n\nThe optional RETURNING clause causes UPDATE to compute and return value(s) based on each row\nactually updated. Any expression using the table's columns, and/or columns of other tables\nmentioned in FROM, can be computed. The new (post-update) values of the table's columns are\nused. The syntax of the RETURNING list is identical to that of the output list of SELECT.\n\nYou must have the UPDATE privilege on the table, or at least on the column(s) that are listed\nto be updated. You must also have the SELECT privilege on any column whose values are read in\nthe expressions or condition.\n",
            "subsections": []
        },
        "PARAMETERS": {
            "content": "withquery\nThe WITH clause allows you to specify one or more subqueries that can be referenced by\nname in the UPDATE query. See Section 7.8 and SELECT(7) for details.\n\ntablename\nThe name (optionally schema-qualified) of the table to update. If ONLY is specified\nbefore the table name, matching rows are updated in the named table only. If ONLY is not\nspecified, matching rows are also updated in any tables inheriting from the named table.\nOptionally, * can be specified after the table name to explicitly indicate that\ndescendant tables are included.\n\nalias\nA substitute name for the target table. When an alias is provided, it completely hides\nthe actual name of the table. For example, given UPDATE foo AS f, the remainder of the\nUPDATE statement must refer to this table as f not foo.\n\ncolumnname\nThe name of a column in the table named by tablename. The column name can be qualified\nwith a subfield name or array subscript, if needed. Do not include the table's name in\nthe specification of a target column — for example, UPDATE tablename SET tablename.col\n= 1 is invalid.\n\nexpression\nAn expression to assign to the column. The expression can use the old values of this and\nother columns in the table.\n\nDEFAULT\nSet the column to its default value (which will be NULL if no specific default expression\nhas been assigned to it). An identity column will be set to a new value generated by the\nassociated sequence. For a generated column, specifying this is permitted but merely\nspecifies the normal behavior of computing the column from its generation expression.\n\nsub-SELECT\nA SELECT sub-query that produces as many output columns as are listed in the\nparenthesized column list preceding it. The sub-query must yield no more than one row\nwhen executed. If it yields one row, its column values are assigned to the target\ncolumns; if it yields no rows, NULL values are assigned to the target columns. The\nsub-query can refer to old values of the current row of the table being updated.\n\nfromitem\nA table expression allowing columns from other tables to appear in the WHERE condition\nand update expressions. This uses the same syntax as the FROM clause of a SELECT\nstatement; for example, an alias for the table name can be specified. Do not repeat the\ntarget table as a fromitem unless you intend a self-join (in which case it must appear\nwith an alias in the fromitem).\n\ncondition\nAn expression that returns a value of type boolean. Only rows for which this expression\nreturns true will be updated.\n\ncursorname\nThe name of the cursor to use in a WHERE CURRENT OF condition. The row to be updated is\nthe one most recently fetched from this cursor. The cursor must be a non-grouping query\non the UPDATE's target table. Note that WHERE CURRENT OF cannot be specified together\nwith a Boolean condition. See DECLARE(7) for more information about using cursors with\nWHERE CURRENT OF.\n\noutputexpression\nAn expression to be computed and returned by the UPDATE command after each row is\nupdated. The expression can use any column names of the table named by tablename or\ntable(s) listed in FROM. Write * to return all columns.\n\noutputname\nA name to use for a returned column.\n",
            "subsections": []
        },
        "OUTPUTS": {
            "content": "On successful completion, an UPDATE command returns a command tag of the form\n\nUPDATE count\n\nThe count is the number of rows updated, including matched rows whose values did not change.\nNote that the number may be less than the number of rows that matched the condition when\nupdates were suppressed by a BEFORE UPDATE trigger. If count is 0, no rows were updated by\nthe query (this is not considered an error).\n\nIf the UPDATE command contains a RETURNING clause, the result will be similar to that of a\nSELECT statement containing the columns and values defined in the RETURNING list, computed\nover the row(s) updated by the command.\n",
            "subsections": []
        },
        "NOTES": {
            "content": "When a FROM clause is present, what essentially happens is that the target table is joined to\nthe tables mentioned in the fromitem list, and each output row of the join represents an\nupdate operation for the target table. When using FROM you should ensure that the join\nproduces at most one output row for each row to be modified. In other words, a target row\nshouldn't join to more than one row from the other table(s). If it does, then only one of the\njoin rows will be used to update the target row, but which one will be used is not readily\npredictable.\n\nBecause of this indeterminacy, referencing other tables only within sub-selects is safer,\nthough often harder to read and slower than using a join.\n\nIn the case of a partitioned table, updating a row might cause it to no longer satisfy the\npartition constraint of the containing partition. In that case, if there is some other\npartition in the partition tree for which this row satisfies its partition constraint, then\nthe row is moved to that partition. If there is no such partition, an error will occur.\nBehind the scenes, the row movement is actually a DELETE and INSERT operation.\n\nThere is a possibility that a concurrent UPDATE or DELETE on the row being moved will get a\nserialization failure error. Suppose session 1 is performing an UPDATE on a partition key,\nand meanwhile a concurrent session 2 for which this row is visible performs an UPDATE or\nDELETE operation on this row. In such case, session 2's UPDATE or DELETE will detect the row\nmovement and raise a serialization failure error (which always returns with an SQLSTATE code\n'40001'). Applications may wish to retry the transaction if this occurs. In the usual case\nwhere the table is not partitioned, or where there is no row movement, session 2 would have\nidentified the newly updated row and carried out the UPDATE/DELETE on this new row version.\n\nNote that while rows can be moved from local partitions to a foreign-table partition\n(provided the foreign data wrapper supports tuple routing), they cannot be moved from a\nforeign-table partition to another partition.\n",
            "subsections": []
        },
        "EXAMPLES": {
            "content": "Change the word Drama to Dramatic in the column kind of the table films:\n\nUPDATE films SET kind = 'Dramatic' WHERE kind = 'Drama';\n\nAdjust temperature entries and reset precipitation to its default value in one row of the\ntable weather:\n\nUPDATE weather SET templo = templo+1, temphi = templo+15, prcp = DEFAULT\nWHERE city = 'San Francisco' AND date = '2003-07-03';\n\nPerform the same operation and return the updated entries:\n\nUPDATE weather SET templo = templo+1, temphi = templo+15, prcp = DEFAULT\nWHERE city = 'San Francisco' AND date = '2003-07-03'\nRETURNING templo, temphi, prcp;\n\nUse the alternative column-list syntax to do the same update:\n\nUPDATE weather SET (templo, temphi, prcp) = (templo+1, templo+15, DEFAULT)\nWHERE city = 'San Francisco' AND date = '2003-07-03';\n\nIncrement the sales count of the salesperson who manages the account for Acme Corporation,\nusing the FROM clause syntax:\n\nUPDATE employees SET salescount = salescount + 1 FROM accounts\nWHERE accounts.name = 'Acme Corporation'\nAND employees.id = accounts.salesperson;\n\nPerform the same operation, using a sub-select in the WHERE clause:\n\nUPDATE employees SET salescount = salescount + 1 WHERE id =\n(SELECT salesperson FROM accounts WHERE name = 'Acme Corporation');\n\nUpdate contact names in an accounts table to match the currently assigned salesmen:\n\nUPDATE accounts SET (contactfirstname, contactlastname) =\n(SELECT firstname, lastname FROM salesmen\nWHERE salesmen.id = accounts.salesid);\n\nA similar result could be accomplished with a join:\n\nUPDATE accounts SET contactfirstname = firstname,\ncontactlastname = lastname\nFROM salesmen WHERE salesmen.id = accounts.salesid;\n\nHowever, the second query may give unexpected results if salesmen.id is not a unique key,\nwhereas the first query is guaranteed to raise an error if there are multiple id matches.\nAlso, if there is no match for a particular accounts.salesid entry, the first query will set\nthe corresponding name fields to NULL, whereas the second query will not update that row at\nall.\n\nUpdate statistics in a summary table to match the current data:\n\nUPDATE summary s SET (sumx, sumy, avgx, avgy) =\n(SELECT sum(x), sum(y), avg(x), avg(y) FROM data d\nWHERE d.groupid = s.groupid);\n\nAttempt to insert a new stock item along with the quantity of stock. If the item already\nexists, instead update the stock count of the existing item. To do this without failing the\nentire transaction, use savepoints:\n\nBEGIN;\n-- other operations\nSAVEPOINT sp1;\nINSERT INTO wines VALUES('Chateau Lafite 2003', '24');\n-- Assume the above fails because of a unique key violation,\n-- so now we issue these commands:\nROLLBACK TO sp1;\nUPDATE wines SET stock = stock + 24 WHERE winename = 'Chateau Lafite 2003';\n-- continue with other operations, and eventually\nCOMMIT;\n\nChange the kind column of the table films in the row on which the cursor cfilms is currently\npositioned:\n\nUPDATE films SET kind = 'Dramatic' WHERE CURRENT OF cfilms;\n",
            "subsections": []
        },
        "COMPATIBILITY": {
            "content": "This command conforms to the SQL standard, except that the FROM and RETURNING clauses are\nPostgreSQL extensions, as is the ability to use WITH with UPDATE.\n\nSome other database systems offer a FROM option in which the target table is supposed to be\nlisted again within FROM. That is not how PostgreSQL interprets FROM. Be careful when porting\napplications that use this extension.\n\nAccording to the standard, the source value for a parenthesized sub-list of target column\nnames can be any row-valued expression yielding the correct number of columns.  PostgreSQL\nonly allows the source value to be a row constructor or a sub-SELECT. An individual column's\nupdated value can be specified as DEFAULT in the row-constructor case, but not inside a\nsub-SELECT.\n\n\n\nPostgreSQL 14.23                                2026                                       UPDATE(7)",
            "subsections": []
        }
    },
    "summary": "UPDATE - update rows of a table",
    "flags": [],
    "examples": [
        "Change the word Drama to Dramatic in the column kind of the table films:",
        "UPDATE films SET kind = 'Dramatic' WHERE kind = 'Drama';",
        "Adjust temperature entries and reset precipitation to its default value in one row of the",
        "table weather:",
        "UPDATE weather SET templo = templo+1, temphi = templo+15, prcp = DEFAULT",
        "WHERE city = 'San Francisco' AND date = '2003-07-03';",
        "Perform the same operation and return the updated entries:",
        "UPDATE weather SET templo = templo+1, temphi = templo+15, prcp = DEFAULT",
        "WHERE city = 'San Francisco' AND date = '2003-07-03'",
        "RETURNING templo, temphi, prcp;",
        "Use the alternative column-list syntax to do the same update:",
        "UPDATE weather SET (templo, temphi, prcp) = (templo+1, templo+15, DEFAULT)",
        "WHERE city = 'San Francisco' AND date = '2003-07-03';",
        "Increment the sales count of the salesperson who manages the account for Acme Corporation,",
        "using the FROM clause syntax:",
        "UPDATE employees SET salescount = salescount + 1 FROM accounts",
        "WHERE accounts.name = 'Acme Corporation'",
        "AND employees.id = accounts.salesperson;",
        "Perform the same operation, using a sub-select in the WHERE clause:",
        "UPDATE employees SET salescount = salescount + 1 WHERE id =",
        "(SELECT salesperson FROM accounts WHERE name = 'Acme Corporation');",
        "Update contact names in an accounts table to match the currently assigned salesmen:",
        "UPDATE accounts SET (contactfirstname, contactlastname) =",
        "(SELECT firstname, lastname FROM salesmen",
        "WHERE salesmen.id = accounts.salesid);",
        "A similar result could be accomplished with a join:",
        "UPDATE accounts SET contactfirstname = firstname,",
        "contactlastname = lastname",
        "FROM salesmen WHERE salesmen.id = accounts.salesid;",
        "However, the second query may give unexpected results if salesmen.id is not a unique key,",
        "whereas the first query is guaranteed to raise an error if there are multiple id matches.",
        "Also, if there is no match for a particular accounts.salesid entry, the first query will set",
        "the corresponding name fields to NULL, whereas the second query will not update that row at",
        "all.",
        "Update statistics in a summary table to match the current data:",
        "UPDATE summary s SET (sumx, sumy, avgx, avgy) =",
        "(SELECT sum(x), sum(y), avg(x), avg(y) FROM data d",
        "WHERE d.groupid = s.groupid);",
        "Attempt to insert a new stock item along with the quantity of stock. If the item already",
        "exists, instead update the stock count of the existing item. To do this without failing the",
        "entire transaction, use savepoints:",
        "BEGIN;",
        "-- other operations",
        "SAVEPOINT sp1;",
        "INSERT INTO wines VALUES('Chateau Lafite 2003', '24');",
        "-- Assume the above fails because of a unique key violation,",
        "-- so now we issue these commands:",
        "ROLLBACK TO sp1;",
        "UPDATE wines SET stock = stock + 24 WHERE winename = 'Chateau Lafite 2003';",
        "-- continue with other operations, and eventually",
        "COMMIT;",
        "Change the kind column of the table films in the row on which the cursor cfilms is currently",
        "positioned:",
        "UPDATE films SET kind = 'Dramatic' WHERE CURRENT OF cfilms;"
    ],
    "see_also": []
}