CODEWITHSUNDEEP
sqloracle-database
Peter
Peter

Reputation: 21

Update a table based on a temporary table in oracle

I want to update a column of a table(national_id from Personal_info) based on a value of a temporary table(nationalid from Tmp_Tbl).

Below Two tables having a pk/fk relation with each other using cid,user_id.

Users(cid,user_name)

Personal_info(user_id,national_id)

Temporary table also have a relation to Users table using user_name which is unique.

Tmp_Tbl(user_name,nationalid)

As it is oracle , i don't want to use of update join and also not merge syntax as i have the solution already.i am looking for a simple update query to use.

Upvotes: 1

Views: 5605

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270443

In Oracle, you can do this using subqueries, but you need to be careful. You need a similar subquery in the set and the where:

UPDATE Personal p
   SET national_id = (SELECT t.nationalid
                      FROM tmp_tbl t JOIN
                           users u
                           ON t.user_name = u.user_name
                      WHERE u.cid = p.user_id
                     )
   WHERE EXISTS (SELECT 1
                 FROM tmp_tbl t JOIN
                      users u
                      ON t.user_name = u.user_name
                 WHERE u.cid = p.user_id
                );

If you leave out the WHERE clause, you will erase the national_id of users that are not in the temp table.

This assumes that you just want to update values for existing users. If you want to both add new users and insert values, then the code is more complicated.

Upvotes: 1

Related Questions