How can I implement conditional inserting in Oracle?

Question

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?

Answer 1

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)

Answer 2

UPDAT UZRTOO.TABTOO.IMPKNOW TEATOO
SETT DEZIGNASHUN IZ EQL DOTZ
WHAR IS HVING (SLEKT NUTHN
                FRUM UZRWON.TABWON TEAWON
                WAR IS HVING TEAWON.IMPKNOW IZ EQL TEATOO.IMPKNOW)

Update

A word of explanation; the original phrasing of the question was full of text-message-style abbreviations, and included a LOLCAT photo. So, in good fun, it seemed OK to translate the SQL to SQLOLCODE. No offense intended to anyone.

Answer 3

You would need a set of triggers,

After insert or update:

CREATE OR REPLACE TRIGGER tab1_after_changed
AFTER INSERT OR UPDATE
    ON tab1
    FOR EACH ROW

BEGIN
    DELETE FROM User2.Tab2 WHERE EmpNo=:NEW.EmpNo;
    INSERT INTO User2.Tab2(EmpNo,EmpName,NameDesignation) 
        VALUES (:NEW.EmpNo,:NEW.EmpName, (SELECT DesignationName FROM Designation where DesignationID=:NEW.DesignationID));
END;

I just imagined a table with Designation (DesignationID number, DesignationName varchar2(xx)), and Tab1 having DesignationID(number).