updating table rows in postgres using subquery

Postgres allows:

UPDATE dummy
SET customer=subquery.customer,
    address=subquery.address,
    partn=subquery.partn
FROM (SELECT address_id, customer, address, partn
      FROM  /* big hairy SQL */ ...) AS subquery
WHERE dummy.address_id=subquery.address_id;

This syntax is not standard SQL, but it is much more convenient for this type of query than standard SQL. I believe Oracle (at least) accepts something similar.

Related Posts:

  1. Postgres Error: More than one row returned by a subquery used as an expression
  2. What is the difference between LATERAL JOIN and a subquery in PostgreSQL?
  3. How can I do an UPDATE statement with JOIN in SQL Server?
  4. Nested select statement in SQL Server
  5. missing FROM-clause entry for table
  6. Insert text with single quotes in PostgreSQL
  7. ERROR: there is no unique constraint matching given keys for referenced table “bar”
  8. Oracle SQL: Update a table with data from another table
  9. I want to use CASE statement to update some records in sql server 2005
  10. Simulate CREATE DATABASE IF NOT EXISTS for PostgreSQL?
  11. Postgresql tables exists, but getting “relation does not exist” when querying
  12. postgresql: error duplicate key value violates unique constraint
  13. pg_ctl: no database directory specified and environment variable PGDATA unset
  14. MySQL – UPDATE multiple rows with different values in one query
  15. mysql update column with value from another table
  16. PostgreSQL create table if not exists
  17. Join vs. sub-query
  18. I want to use CASE statement to update some records in sql server 2005
  19. PostgreSQL visual interface similar to phpMyAdmin?
  20. GROUP BY and COUNT using ActiveRecord
  21. Postgresql column reference “id” is ambiguous
  22. ORA-01779: cannot modify a column which maps to a non key-preserved table
  23. Update query using Subquery in Sql Server
  24. What is a postgres superuser
  25. MySQL Error: : ‘Access denied for user ‘root’@’localhost’
  26. What is the difference between “INNER JOIN” and “OUTER JOIN”?
  27. SQL WITH clause example [duplicate]
  28. The wait operation timed out. ASP
  29. SQL query to select dates between two dates
  30. MySQL – Operand should contain 1 column(s)
  31. SQL SELECT WHERE field contains words
  32. MySQL Error: : ‘Access denied for user ‘root’@’localhost’
  33. NOT IN vs NOT EXISTS
  34. Must declare the scalar variable
  35. ORA-00979 not a group by expression
  36. MySQL query String contains
  37. MySQL Cannot Add Foreign Key Constraint
  38. SQL update from one Table to another based on a ID match
  39. Oracle error : ORA-00905: Missing keyword
  40. mysql Foreign key constraint is incorrectly formed error
  41. Can a foreign key be NULL and/or duplicate?
  42. How do composite indexes work?
  43. MySQL “WITH” clause
  44. What is the difference between JOIN and UNION?
  45. MySQL Multiple Joins in one query?
  46. Error Code: 2013. Lost connection to MySQL server during query
  47. “CASE” statement within “WHERE” clause in SQL Server 2008
  48. SQL join on multiple columns in same tables
  49. ORA-00972 identifier is too long alias column name
  50. What is the difference between Scope_Identity(), Identity(), @@Identity, and Ident_Current()?
  51. Sql query – getting rid of hard-coded values
  52. ORA-00918: column ambiguously defined in SELECT *
  53. How To Run A Github Repository?
  54. SQL Switch/Case in ‘where’ clause
  55. Oracle – ORA-01489: result of string concatenation is too long [duplicate]
  56. SQL Server WITH statement
  57. How to run a SQL query on an Excel table?
  58. Algebra Relational sql GROUP BY SORT BY ORDER BY
  59. SQL Server reports ‘Invalid column name’, but the column is present and the query works through management studio
  60. Remote table-Valued Function Calls are not allowed
  61. The backend version is not supported to design database diagrams or tables
  62. Determine ROW that caused “unexpected end of file” error in BULK INSERT?
  63. SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax — PHP — PDO [duplicate]
  64. Difference between View and table in sql
  65. Using group by on multiple columns
  66. What is the purpose of using WHERE 1=1 in SQL statements?
  67. Exclude a column using SELECT * [except columnA] FROM tableA?
  68. Why is SQL server throwing this error: Cannot insert the value NULL into column ‘id’?
  69. ERROR 1148: The used command is not allowed with this MySQL version
  70. Bulk load data conversion error (truncation)
  71. How to order by with union in SQL?
  72. Difference between INNER JOIN and LEFT SEMI JOIN
  73. SQL conditional SELECT
  74. ‘CREATE PROCEDURE’ must be the only statement in the batch (Erro)
  75. Alternative to except in MySQL
  76. DATEDIFF function in Oracle
  77. MySQL – Get row number on select
  78. What does * mean in sql?
  79. How to truncate the text returned for a column in a MySQL query
  80. INSERT VALUES WHERE NOT EXISTS
  81. Using union and order by clause in mysql
  82. Difference between `load data inpath ` and `location` in hive?
  83. How To Write An Inner Join With WP Query
  84. Update user_login, user_nicename, and display_name
  85. Change sticky status of posts from phpMyAdmin
  86. Fetch all Posts where logged in user has commented
  87. How to import a Typo3 database to a wordpress site?
  88. What does the $posts_join filter join to?
  89. Clean up very big and very dirty database
  90. How can I convert everything from category X to have post format Link
  91. Bulk delete WordPress Post and all metadata, etc using SQL query
  92. What steps do I need to take to install a local copy of a live website?
  93. Reset post IDs with all post meta
  94. How to Add or Change Post Title
  95. Why does DROP TABLE-ing the `wp_options` reset my user session?
  96. How to refactor DB queries for better TTFB in WordPress?
  97. query sql-table and change entities
  98. Analog category_and (WP) in sql query
  99. Backtick (MySQL norm) added to SQL Server Query causing error
  100. GRANT SELECT to all tables in postgresql

Leave a Comment

tech