update one table using data in another table

Question

i have table A

mem_id | temp_id | version
--------------------------
1      | 1008    | 1
2      | 1009    | 1
3      | 1010    | 1
4      | 1021    | 1

and table B

temp_id | base id | desc
--------------------------
1008    | 720     | GP
1009    | 720     | GP
1010    | 720     | GP
1021    | 720     | GP

I want to do an update statement that will update the temp_id for all mem_id in table A to only temp_id = 1008.

update table a
set a.temp_id = (minimum of temp_id in table b)
where a.temp_id in (select temp_id in table b where b.tempid (min)

i want it to check for the minimum value in table b_temp_id and then set all values in table A_temp_id to be the same.

I don't understand. You want to determine the minimum temp_id in table B. That is 1008. Then you want to update all rows in table A that have that temp_id with that temp_id? update a set temp_id = 1008 where temp_id = 1008? That makes no sense. Please show the table A content after the update. — Thorsten Kettner
– Thorsten Kettner, Commented Aug 22, 2019 at 7:20

Barbaros Özhan · Accepted Answer · 2019-08-22 07:29:55Z

1

You can directly update by

update tableA A
   set temp_id = (select min(temp_id) from tableB)

for the whole data set without any restriction

answered Aug 22, 2019 at 6:51

Barbaros Özhan

65.9k11 gold badges36 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

sorry i forgot to mention that I use oracle

There is no need for a WITH clause. You simply can put it into the subquery dbfiddle.uk/…

OK I rearranged for Oracle DB

of course @S-Man , that's the way I like :) anyway, thanks.