update query from select statement only update if the field is empty

Refresh

December 2018

Views

1.3k time

1

I am trying to write a sql statement to update a column of a table from another tables column. But I only want to update the column if its empty.

For example:

UPDATE
    Table
SET
    Table.col1 = other_table.col1,
FROM
    Table
INNER JOIN
    other_table
ON
    Table.id = other_table.id

but I want to set the Table.col1 value only if that value is empty. Whats the best way to do this?

1 answers

4

Define empty?

But really all you need is a WHERE clause like

UPDATE Table
   SET Table.col1 = other_table.col1,
  FROM Table
       INNER JOIN
       other_table ON Table.id = other_table.id
 WHERE Table.col IS NULL  --or whatever your empty condition is

In Postgre you may need a different syntax (How to do an update + join in PostgreSQL?):

UPDATE Table
   SET Table.col1 = other_table.col1,
  FROM Table
      ,other_table 
 WHERE Table.id = other_table.id
   AND Table.col IS NULL  --or whatever your empty condition is