I have the following query that does what I want in Access, but I need

[szeiss]

I have the following query that does what I want in Access, but I need to convert it to SQL Plus and am having problems, can anyone help.<br><br>UPDATE DISTINCTROW CIP_SHERRY_WORKTABLE LEFT JOIN CIP_SHERRY_EMPLOYEE_INFO ON CIP_SHERRY_WORKTABLE.PIDM = CIP_SHERRY_EMPLOYEE_INFO.EMPLOYEE_ID SET CIP_SHERRY_EMPLOYEE_INFO.EMPLYSTAT = &quot;T&quot;<br>WHERE (((CIP_SHERRY_EMPLOYEE_INFO.EMPLOYEE_ID) Is Null));<br><br>Thanks,<br>Sherry

 

[carp]

I don't work with Access, but I BELIEVE what you are trying to do can be done with this:<br><br>UPDATE CIP_SHERRY_WORKTABLE <br>SET EMPLYSTAT = &quot;T&quot;<br>WHERE EMPLOYEE_ID Is Null;<br><br>Definitely test and verify this before committing!

 

[carp]

My mistake!&nbsp;&nbsp;Disregard the previous post.&nbsp;&nbsp;Try this instead:<br><br>UPDATE CIP_SHERRY_EMPLOYEE_INFO <br>SET EMPLYSTAT = &quot;T&quot;<br>WHERE EMPLOYEE_ID Is Null;

 

[szeiss]

That's part of it.&nbsp;&nbsp;But your missing where I have to join the tables.&nbsp;&nbsp;I've got 2 temp tables, sherry_employee_info and sherry_worktable.&nbsp;&nbsp;I'm trying to find records that are in the sherry_employee_info table and not in the sherry_worktable table.&nbsp;&nbsp;For the records that are not in the sherry_worktable, I need to update the emplystat to 'T'.<br><br>Thanks,

 

[carp]

UPDATE cip_sherry_employee_info<br>SET emplystat = 'T'<br>WHERE employee_id IN<br>(SELECT employee_id FROM sherry_employee_info<br>&nbsp;MINUS<br>&nbsp;SELECT pidm FROM sherry_worktable);<br><br>Bear with me - I'm trying to figure out the Access syntax (which I'm not too familiar with) and trying to come up with an equivilent in Oracle.&nbsp;&nbsp;I'm guessing that PIDM and EMPLOYEE_ID are the common columns between the two tables.&nbsp;&nbsp;I think this command will do the job, although I've no doubt somebody will come up with a more efficient approach.&nbsp;&nbsp;But it's late, and this is what comes to mind.&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

 

[bigscouse]

This may be worth a try, I would recomend that you run the query as a select at first and then put the update/set in when you are happy with the selection.<br><br>UPDATE cip_sherry_employee_info<br>&nbsp;&nbsp;&nbsp;SET emplystat = 'T'<br>&nbsp;WHERE employee_id NOT IN<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;(SELECT pidm <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;FROM sherry_worktable)<br>&nbsp;&nbsp;&nbsp;AND employee_id IS NOT NULL;<br><br> <p>Ged Jones<br><a href=mailto:gedejones@hotmail.com>gedejones@hotmail.com</a><br><a href= > </a><br>