Update value in one table based on value of another table

All we need is an easy explanation of the problem, so here it is.

I would like to compare two tables, t1 & t2, and change the value of a columns row value in t1 based on the condition of a column in t2.

t1:

fid vul agg_cost
1 0.6 1400
2 0.5 1300
3 0.8 1200

t2:

fid vul agg_cost
1 0.2 200
2 0.5 2000
3 0.8 450

desired outcome of t1:

fid vul agg_cost
1 1.0 1400
2 0.5 1300
3 1.0 1200

I have used this query but it makes the wrong changes:

UPDATE t1 
SET vul = 1.0
FROM t2
WHERE t1.agg_cost > t2.agg_cost;

& the inner join gives me table name t1 specified more than once.

UPDATE t1 
SET vul = 1  
FROM t1  
INNER JOIN t2 ON t1.fid = t2.fid 
WHERE t1.agg_cost > t2.agg_cost; 

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

If I get it right, you want to update t1 if there exist a row in t2 with the same fid and a bigger agg_cost

UPDATE t1 
    SET vul = 1  
WHERE EXISTS (
    SELECT 1
    FROM t2 
    WHERE t1.fid = t2.fid 
      AND t1.agg_cost > t2.agg_cost
);

Method 2

UPDATE t1 
   SET vul = 1
 WHERE fid IN (SELECT t2.fid 
                    FROM t2
                    WHERE t1.agg_cost > t2.agg_cost);

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply