Update table with the same column

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)

Update table with the same column

Author

Topic

VipinMitta
Starting Member

12 Posts

Posted - 2010-10-08 : 07:00:05

One table having the rows like below

M
M
M
F
F

I NEED A SINGLE UPDATE QUERY WHICH WILL UPDATE M AS F AND F AS M WITH OUT USING CASE

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-10-08 : 07:37:08

Why not using CASE?
It is the best way for this!

No, you're never too old to Yak'n'Roll if you're too young to die.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-10-08 : 08:22:22

I concur.

Here is one way to do what you asked for. No case in sight........


DECLARE @foo TABLE (
	[gender] CHAR(1)
	)

INSERT @foo
          SELECT 'F'
UNION ALL SELECT 'F'
UNION ALL SELECT 'M'
UNION ALL SELECT 'M'
UNION ALL SELECT 'M'

SELECT * FROM @foo

UPDATE @foo SET
	[gender] = CHAR(UNICODE([gender]) + ISNULL(NULLIF(UNICODE([gender]), 70) - 84, 7))

SELECT * FROM @foo

BTW -- this is a joke. USE A CASE EXPRESSION
Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-10-08 : 08:27:46

And another stupid way:


DECLARE @foo TABLE (
	[gender] CHAR(1)
	)

INSERT @foo
          SELECT 'F'
UNION ALL SELECT 'F'
UNION ALL SELECT 'M'
UNION ALL SELECT 'M'
UNION ALL SELECT 'M'

SELECT * FROM @foo


UPDATE f SET
	[gender] = mp.[newGender]
FROM
	@foo AS f
	JOIN (
		SELECT
			'M' AS [gender]
			, 'F' AS [newGender]
		UNION SELECT
			'F' AS [gender]
			, 'M' AS [newGender]
		)
		AS mp ON mp.[gender] = f.[gender]

SELECT * FROM @foo

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Edgemere5
Starting Member

4 Posts

Posted - 2010-10-09 : 17:17:36

What did you do - give everybody a sex change operation?

update bozo
SET gender = xx.gender
FROM bozo
JOIN bozo xx
on bozo.gender <> xx.gender

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources