0

I'd like to do something like this:

update t1 set t1.column1 = 'changed'
from left outer join t2 on t1.dataid = t2.dataid
where t2.column2 != 'foo';

Basically, I need to check something in t2 when deciding which t1 records to update. How can I make this syntactically correct?

Milen A. Radev
  • 60,241
  • 22
  • 105
  • 110
BLuFeNiX
  • 2,496
  • 2
  • 20
  • 40
  • There is an example for such an update in the manual: http://www.postgresql.org/docs/current/static/sql-update.html –  Oct 30 '13 at 17:13

2 Answers2

1

Try something like:

update t1 
set t1.column1 = 'changed'
from t2
where t1.dataid = t2.dataid
  and t2.column2 != 'foo';

If you really need outer join try something like:

update t1
set t1.column1 = 'changed'
from (select 1 as dummy) dummy_table
left join t2 on t1.dataid = t2.dataid
            and t2.column2 != 'foo';

Havent tested - it may not work.

Another possible way is to do a inner join to a "copy" of t1 and then do a left join to t2

Ihor Romanchenko
  • 26,995
  • 8
  • 48
  • 44
1

Rather than doing a LEFT JOIN, it's simpler to establish the correlation to the other table in the WHERE clause, when the values from the correlated table don't have to be transferred to the updated table.

It would look like this:

update t1 set t1.column1 = 'changed'
where not exists (select 1 from t2 where t1.dataid = t2.dataid and t2.column2='foo');
Daniel Vérité
  • 58,074
  • 15
  • 129
  • 156
  • This almost works, here is my correction: `update t1 set column1 = 'changed' where exists (select 1 from t2 where t1.dataid = t2.dataid and t2.column2 != 'foo');` (removed the `t1.` from the `t1.column1` which was causing an error, and changed the where logic to fit my specific case.) Thanks! – BLuFeNiX Oct 31 '13 at 15:13