How to write a sql update query for below condition?-CodePudding

I have 2 tables. First is employee table and has (id,emp_num,name,address_id,text) columns. Second is address table with (adress_id,emp_num,adress,state) columns.

Now when I am inserting/updating a new record into employee table, I want to update text field for all addresses with same state(from address table) of that employee.

Thanks

CodePudding user response：

    UPDATE employee e
SET e.text = (SELECT a.state FROM address a WHERE a.address_id = e.address_id)
WHERE e.emp_num IN (SELECT a.emp_num FROM address a WHERE a.state = (SELECT a.state FROM address a WHERE a.address_id = e.address_id));

CodePudding user response：

To me, it looks as if you need a row-level trigger which fires whenever you insert or update a row in employee table; using :new.address_id, fetch state from address table and put it into employee's text column:

create or replace trigger trg_biu_emp
  before insert or update on employee
  for each row
begin
  select a.state
    into :new.text
    from address a
    where a.address_id = :new.address_id;
end;
/