7

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?

add-semi-colons
  • 18,094
  • 55
  • 145
  • 232

1 Answers1

11

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
Community
  • 1
  • 1
Karl Kieninger
  • 8,841
  • 2
  • 33
  • 49
  • To capitalize on @KarlKieninger's statement, there's a major difference between a nulled entry and an empty entry (i.e., ''). – Tiny Haitian May 07 '14 at 20:43
  • why would it throws the error of "table name Table spcecified more than once -> here Table is my table name. – add-semi-colons May 07 '14 at 20:56
  • 1
    @Null-Hypothesis because you make a join in an update statement. This can be solved (but usually different between dbms). So what's your dbms ? – Raphaël Althaus May 07 '14 at 21:08
  • @RaphaëlAlthaus i am using postgres – add-semi-colons May 07 '14 at 21:11
  • Oh, in postgre it's different. you need to change the join syntax a bit, but your aander is still to tack on the appropriate where empty condition. See http://stackoverflow.com/questions/7869592/how-to-do-an-update-join-in-postgresql – Karl Kieninger May 07 '14 at 21:58