# 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 🙂