Databases
StackOverflow2013
Postsdbo
POWhat is the best way to run N independent column updates in PostgreSQL? What is the best way to do it in the SQL spec?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POWhat is the best way to run N independent column updates in PostgreSQL? What is the best way to do it in the SQL spec?
    primarykey
    data
    text
    <p>I'm looking for a more efficient way to run many columns updates on the same table like this:</p> <pre><code>UPDATE TABLE table SET col = regexp_replace( col, 'foo', 'bar' ) WHERE regexp_match( col, 'foo' ); </code></pre> <p>Such that <code>foo</code>, and <code>bar</code>, will be a combination of 40 different regex-replaces. I doubt even 25% of the dataset needs to be updated at all, but what I'm wanting to know is it is possible to cleanly achieve the following in SQL.</p> <ul> <li>A single pass update</li> <li>A single match of the regex, triggers a single replace</li> <li><strong>Not</strong> running all possible regexp_replaces if only one matches</li> <li><strong>Not</strong> updating all columns if only one needs the update</li> <li><strong>Not</strong> updating a row if no column has changed</li> </ul> <p>I'm also curious, I know in MySQL (bear with me)</p> <pre><code>UPDATE foo SET bar = 'baz' </code></pre> <p>Has an implicit <code>WHERE bar != 'baz'</code> clause</p> <p>However, in PostgreSQL I know this doesn't exist: I think I could at least answer one of my questions if I knew how to skip a single row's update if the target columns weren't updated.</p> <p>Something like</p> <pre><code>UPDATE TABLE table SET col = *temp_var* = regexp_replace( col, 'foo', 'bar' ) WHERE col != *temp_var* </code></pre>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USJuan Mellado
    1. USEvan Carroll
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POWhat is the best way to run N independent column updates in PostgreSQL? What is the best way to do it in the SQL spec?
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POWhat is the best way to run N independent column updates in PostgreSQL? What is the best way to do it in the SQL spec?
      1. This table or related slice is empty.
      1. VTUpMod
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload