0

I have two tables: houses and users

houses has a foreign key to user via the column user_id. It has also a column called active which is either 'yes' or 'no'

users has a column called active which is either 'yes' or 'no'

I want to run a query to update all the houses active column to be 'no' as long as their associated user has 'no' in its active column AND the house active column is not already 'no'.

I know how to update based on the users table value, but I don't know how to do it when the conditions are on both an associated table and its own table.

Hommer Smith
  • 26,772
  • 56
  • 167
  • 296

4 Answers4

1

In Postgres, you would do:

update houses h
    set active = 'no'
    from users u
    where h.user_id = u.user_id and h.active <> 'no' and u.active = 'no';

Note that <> 'no' should perhaps be is distinct from 'no', if you want to take NULL values into account.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

use this syntax:

update a 
set field x = case when fieldy = somecondition then A else fieldy end
from table1 a join table2 b on a.someid=b.someid
Daniel Marcus
  • 2,686
  • 1
  • 7
  • 13
0

You can inner join the two tables and then update house activity based on the two conditions. 

    update t1
    set t1.active = 'no'
    from house t1
    inner join users t2
    on t1.user_id = t2.user_id
    where t2.active = 'no'
    and not t1.active = 'no'
MJZ
  • 13
  • 4
0

You might want to use a trigger, so whenever you execute an update on the users 'active' column, it will trigger and update the houses table.

Take a look on this example

Lazaro Henrique
  • 527
  • 5
  • 7