URGENT HELP PLEASE - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

URGENT HELP PLEASE

Author

Topic

mrmmartian
Starting Member

1 Post

Posted - 2012-08-07 : 00:41:01

I have this 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

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-07 : 06:55:11

quote:
Originally posted by mrmmartian

I have this 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'

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2012-08-07 : 06:58:06

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'

--------------------------
http://connectsql.blogspot.com/

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-07 : 07:17:35

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! :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources