Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How can I implement conditional updating in Oracle?

I'm new to oracle and having a problem with one of my SQL Queries.

There are 2 Users: User1 and User2:

     Tab1               Tab2
    --------            -------- 
    
    EmpNo               EmpNo
    EmpName             EmpName
    ContactNo           Salary
    Location    

User2 has all privileges in User1.Tab1, and there is no foreign key relationship between the two tables.

The Problem:

I wanted to add a column in tab2 "NameDesignation" And I wanted to insert the value in this column after checking the following condition:

WHEN User1.Tab1.EmpNo = User2.Tab2.EmpNo THEN
   INSERT INTO Tab2 VALUES (&designation)

I really have no idea how to do this, and was hoping for a little help. Any thoughts?

like image 977
naaz Avatar asked Feb 25 '09 07:02

naaz


People also ask

How to use the UPDATE statement in Oracle?

Introduction to the Oracle UPDATE statement. To changes existing values in a table, you use the following Oracle UPDATE statement: UPDATE table_name SET column1 = value1, column2 = value2, column3 = value3, ... WHERE condition; Let’s examine the UPDATE statement in detail. First, you specify the name of the table which you want to update.

How do you update an existing table in Oracle?

Introduction to the Oracle UPDATE statement To changes existing values in a table, you use the following Oracle UPDATE statement: UPDATE table_name SET column1 = value1, column2 = value2, column3 = value3,...

What is the syntax for Oracle update with join clause?

Firstly, let us view the syntax for Oracle update with Join clause to update a single table typed as follows: UPDATE table SET Col1 = exp1, Col2 = exp2, ……, ColN = ExpN {WHERE conditional expressions}; The other type of oracle syntax for update with join while updating a single table with data from the other table is as follows: ...

How do you update more than one column in SQL?

If you update more than two columns, you separate each expression column = value by a comma. The value1, value2, or value3 can be literals or a subquery that returns a single value. Note that the UPDATE statement allows you to update as many columns as you want. Third, the WHERE clause determines which rows of the table should be updated.


1 Answers

try this:

update user2.tab2.empno t2
set NameDesignation= &designation
where exists (select ''
              from user1.tab1 t1
              where t1.empno=t2.empno)

(statement updated to match the edited question)

like image 105
Edwin Avatar answered Oct 12 '22 11:10

Edwin