asked on

SQL syntax... Update statement from select?

Hi All,
I want to update a table with the results of a select. How do I do it?
I've got the Nurse and now I need to get the Nurse Supervisor and update the table that has the Nurse already.

UPDATE AUDIT_USE
set NURSE_SUPERV_ID = s.pid,
    NURSE_SUPERV = s.first_name||' '||s.last_name
where
 
select s.first_name,s.last_name, s.pid 
from AUDIT_USE u, PERSON p, person s
where p.pid = u.nurse_id
and s.position_id = p.REPORTS_TO_1 and s.INACTIVE='N'
and NURSE_ID is not null
and NURSE_ID != 999999999
and (NURSE_SUPERV_ID = 999999999 OR NURSE_SUPERV_ID is null)

Open in new window

ASKER CERTIFIED SOLUTION

thiyaguk

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

fjaf

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

BILL Carlisle

ASKER

Awesome! Thanks..

Pratima

Declare @pid int
Declare @fname int
Declare @lname int
Declare @nid int

select @fname = s.first_name,@lname=s.last_name, @pid=s.pid ,
@nid=u.nurse_id
from AUDIT_USE u, PERSON p, person s
where p.pid = u.nurse_id
and s.position_id = p.REPORTS_TO_1 and s.INACTIVE='N'
and NURSE_ID is not null
and NURSE_ID != 999999999
and (NURSE_SUPERV_ID = 999999999 OR NURSE_SUPERV_ID is null)

UPDATE AUDIT_USE
set NURSE_SUPERV_ID = s.pid,
NURSE_SUPERV = s.first_name||' '||s.last_name
where
nurse_id = @nid

BILL Carlisle

ASKER

This is wrong !
ORA-00933: SQL command not properly ended

UPDATE U
set
U.AUDITOR_ID = B.AUDITOR_ID,
U.SUPERVISOR_ID = B.SUPERVISOR_ID,
U.EXAMINER_ID = B.EXAMINER_ID,
U.BILLSTMP = 'PID_UPD'
from billstmp B, audit_use U
where U.audit_use_id = B.audit_use_id