Question 89089 in PostgreSQL Question posted in
The official documentation can be found here.

SQL Query to fill ALL VALUES in a SINGLE column – Postgresql

This may sound basic and often asked, but I’m surprised how often this question is just partially answered.

Assume I have a POSTGRESQL db which has table with a column called "calculation" in a table called "table". This column is currently filled with NULL.

I have all values to fill calculation outside the database. Imagine the values are in a string separated by commas. Now I with to construct an SQL statement that changes ALL values of "calculation", in the order they appear in such a string.

Similar questions here have been asked, and there an infinite amount of tutorials on the internet on how to add values to a database, but they always suggest:

UPDATE calculation FROM table SET calculation=value WHERE ...

The problems with that command is that: (1) it requires a WHERE conditions, which does not exist (we want to update all values not only values that fit a specific condition). (2) Assumes there is only one value for calculation. Thus, it seems to suggest that we have to call one query for every single line.

People asking often gravitate to:

INSERT INTO table (calculation) VALUES ...

which fits the syntax perfectly, but does not work because the column is already there and the rows are already create.

This is a straightforward operation outside SQL. R,python,matlab, and other languages often have the ability to append vectors as columns to their table-equivalents. Or, even better, replace contents of a column by a vector. What am I missing?

EDIT: A MORE CONCRETE EXAMPLE:

Assume the following table already exist in database db, with name "table"

PKID name calculation
1 alfa NULL
2 beta NULL
3 theta NULL

I wish to fill the calculation field with values "akdak","dndja" and "jsnajsna", respectively. Such that:

PKID name calculation
1 alfa akdak
2 beta dndja
3 theta jsnajsna

How can I do that in a single sql query without referencing PKID and name values in a WHERE clause?

Tags:

2

Answers


  1. 0

    You want to execute something that makes use of the ROW_NUMBER() window function to create a correspondance, based on an order, between records:

    WITH ValueList(Calculation) AS (
    Values('akdak'),('dndja'),('jsnajna')
)
UPDATE MyTable
SET Calculation = V.Calculation
FROM (
    SELECT Name, ROW_NUMBER() OVER (ORDER BY Name)
    FROM MyTable
) T(Name, rownum)
JOIN (
    SELECT Calculation, ROW_NUMBER() OVER (ORDER BY Calculation)
    FROM ValueList
) V(Calculation, rownum)
ON T.rownum = V.rownum
WHERE MyTable.Name = T.Name

    The JOIN will make it so the value count does not need to match the records in the table. Extra values will be ignored, the calculation column of extra record will remain null.

    Note: I have put WHERE MyTable.Name = T.Name at the end of the query but depending of which columns are UNIQUE and/or NOT NULL and what precise behavior you want, you may prefer to use WHERE MyTable.PKID = T.PKID.

    Login or Signup to reply.
  2. 0

    Based on the accepted answer it seems the real question is how to perform a batch update with a single query.

    For lots of data (thousands or more), the typical solution is to insert the data into a staging table with eg COPY and then update the target table by JOINing on the ID values. Since primary keys are indexed, the JOIN will be fast:

    UPDATE Target
Set 
   Calculation1=staging.Calculation1,
   ...
FROM Source INNER JOIN Target
    ON Source.ID=Target.ID

    For a smaller number of rows a table value constructor can be used to emit the ID and new values :

    UPDATE Target
Set 
   Calculation1=staging.Calculation1,
   ...
FROM (
        VALUES
        (1, 'London'),
        (2, 'Rio de Janeiro'),
        (3, 'Tokyo')
     ) as Source(ID, Field1) 
INNER JOIN Target
    ON Source.ID=Target.ID;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search