SQL Update Table Help

[Anon Anon]

Hey there Clarity Pros, I have a question  around SQL updates.   I'm attempting to do the following:  Based on a resource records termination date, mark their resource record as inactive on both the srm_resources side and the cmn_sec users side.  My select statement below identifies the users I need to inactivate, now I just need the right sql to perform the update or insert to set is_active to '0' on srm_resources for that specific resource and also set user_status_id = '202' on cmn_sec_users for that same resource.  Can this be done?   What do I need to do?   Many thanks!    SELECT GETDATE() DATE, r.USER_ID, c.USER_NAME, r.FIRST_NAME, r.LAST_NAME, r.DATE_OF_HIRE, r.DATE_OF_TERMINATION, r.IS_ACTIVE, c.USER_STATUS_ID
FROM niku.CMN_SEC_USERS C, niku.SRM_RESOURCES R
WHERE c.ID = r.USER_ID
AND r.DATE_OF_TERMINATION = CONVERT(nvarchar(30), (GETDATE()+1), 101)  

[Anon Anon]

Doing SQL updates in the database is very, very unsupported.  If you are not completely sure what you are doing the advice is don't.  CA might be able to provide you with a support-approved method, but don't hold your breath.    The alternative, and supported, way to do this would be with a GEL script, updating the resources using XOG.    Mark  

[Anon Anon]

I figured that might be the case.     So GEL can identify the records needed and xog can commit the changes?

[Anon Anon]

Hi,  There might be many better ways but as i don't have the sytem here to check that.This is an static way and may be safe.  --Pls always cross check before doing an DML--- First select the resources id which you are going to Inactive and store in textpad and put an comma.(50001,30004,3005)    1. select id from srm_resources where DATE_OF_TERMINATION = CONVERT(nvarchar(30), (GETDATE()+1), 101);    ---------- Update the resource as In active  2. update srm_resources set is_active=0 where id in (50001,30004,3005);  -- -- Update the user status=202  3. update cmn_sec_users set USER_STATUS_ID = 202 where id in (select user_id from srm_resources where id in (50001,30004,3005)) ;  cheers,sundar

[Anon Anon]

^ not really the best way, too much manual messing about in textpad etc!   :-)    If you are doing the (UNSUPPORTED!!!!) direct SQL update you'd just need to do something like;  UPDATE CMN_SEC_USERS SET USER_STATUS_ID = 202WHERE ID IN(
SELECT USER_IDFROM SRM_RESOURCES r WHERE  AND r.DATE_OF_TERMINATION = CONVERT(nvarchar(30), (GETDATE()+1), 101))  and then  UPDATE SRM_RESOURCES SET IS_ACTIVE = 0WHERE   DATE_OF_TERMINATION = CONVERT(nvarchar(30), (GETDATE()+1), 101)    (assuming my MS-SQL is right there??? I write Oracle not MSSQL, all that CONVERT and GETDATE stuff is meaningless to me!!!!)  --  Since this is UNSUPPORTED you'd have to get CA-approval for this... or build the (SUPPORTED) GEL / XOG solution as described above.  I think I'd try to get the CA-approval myself!  Dave.

[Anon Anon]

Thanks for the feedback, this gives me some paths to go down.   I appreciate the quick responses.

[Deactivated User]

Is the number of resources to be made inactive so big that you cannot use the resources list view with Date of Termination with power filter and Active fields added, number of rows increased to 50 in edit mode?  Martti K. Message Edited by another_martink on 10-12-2009 08:16 PM [left] Message Edited by another_martink on 10-12-2009 09:36 PM [left]

[Anon Anon]

Martti, thanks for that idea... simple and effective but long term I will need an automated process.

[Deactivated User]

SQL Update Table Help

Waiting for an Answer
TomConnery
8
4089
Date: 10/10/11 6:55 AM
By: Jimmy

How would I see the above post?

Martti K.

[Anon Anon]

I recommend using gel/xog rather than update the cmn_sec_users and srm_resources tables directly. This will prevent future integrity issues....