select a.active from sys_scd_profile a inner join sys_scd_profile_activation b on (a.id = b.profile_id) where a.active = 1 and b.date_deactivated is not NULL and b.date_reactivated is NULL and b.date_deactivated < '7 aug 2012'
I need to update all a.active to = 0 where it exists in the select statement.
select a.active from sys_scd_profile a inner join sys_scd_profile_activation b on (a.id = b.profile_id) where a.active = 1 and b.date_deactivated is not NULL and b.date_reactivated is NULL and b.date_deactivated < '7 aug 2012'
I need to update all a.active to = 0 where it exists in the select statement.
Please help. very urgent
You can simply add a NOT in front of the current WHERE clauses like this:
SELECT
a.active
FROM
sys_scd_profile a
INNER JOIN sys_scd_profile_activation b
ON (a.id = b.profile_id)
WHERE
NOT
(
a.active = 1
AND b.date_deactivated IS NOT NULL
AND b.date_reactivated IS NULL
AND b.date_deactivated < '7 aug 2012'
)
Alternatively, you can change each piece of the WHERE clause and use OR instead of AND as in:
WHERE
a.active <> 1
OR b.date_deactivated IS NULL
OR b.date_reactivated IS NOT NULL
OR b.date_deactivated >= '7 aug 2012'
Both should give you identical results, but I like the first one for its simplicity.
Edit: People recommend that when you want to specify dates, you specify them in the YYYYMMDD format - which is unambiguous regardless of language/culture settings. So preferable to use '20120807' instead of '7 aug 2012'
Update s_scd_profile SET active to = 0 FROM a.active from sys_scd_profile a inner join sys_scd_profile_activation b on (a.id = b.profile_id) where a.active = 1 and b.date_deactivated is not NULL and b.date_reactivated is NULL and b.date_deactivated < '7 aug 2012'
Thanks Lion. I interpreted it as op wanted to select everything that didn't get selected by the original query!! Still too early in the morning; I am barely past the REM stage! :)
URGENT HELP PLEASE
Posted - 08/07/2012 : 00:41:01
