update query issue.

Question

update query issue.

Ahmad Osama

SSCertifiable

Points: 7676
More actions
April 17, 2008 at 1:02 am

#71836

Hi All,
I have a table inventory with "invid identity(1,1) int, iLease bit"
How can I update the iLease column in a single update query such that iLease=0 where iLease=1 and iLease=1 where iLease=0 ..
Please help!!
Regards,
[font="Verdana"]Sqlfrenzy[/font]

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

A case statement should do the trick.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Ahmad Osama SSCertifiable Points: 7676 More actions · Answer 2

Well..I think that we cant use CASE statement in update query..however,

any other options excluding case

Regards,
[font="Verdana"]Sqlfrenzy[/font]

HanShi SSC-Dedicated Points: 33507 More actions · Answer 3

You can also use the Bitwise NOT function (~) by using a query like:

UPDATE inventory SET iLease = ~ iLease

Regards,

Hans

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

Ahmad Osama SSCertifiable Points: 7676 More actions · Answer 4

HanShi (4/17/2008)
You can also use the Bitwise NOT function (~) by using a query like:
UPDATE inventory SET iLease = ~ iLease

It works:)

What if I have varchar column .. in place of a Bit i.e

I need to replace "ABC" with "XYZ"

Thanks Hans

Regards,
[font="Verdana"]Sqlfrenzy[/font]

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Ahmad Osama (4/17/2008)
Well..I think that we cant use CASE statement in update query..however,

Why not?

CREATE TABLE [dbo].[TheTable](

[ID] [int] identity,

[TheName] [varchar](50) NULL

)

GO

insert into TheTable (TheName) Values ('Tom')

insert into TheTable (TheName) Values ('Sam')

UPDATE dbo.TheTable SET TheName = CASE thename WHEN 'Tom' THEN 'Sam' WHEN 'Sam' THEN 'Tom' End

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

HanShi SSC-Dedicated Points: 33507 More actions · Answer 6

Ahmad Osama (4/17/2008)
HanShi (4/17/2008)
You can also use the Bitwise NOT function (~) by using a query like:
UPDATE inventory SET iLease = ~ iLease
It works:)
What if I have varchar column .. in place of a Bit i.e
I need to replace "ABC" with "XYZ"
Thanks Hans

Then you need the option as GilaMonster said, using the CASE

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

Ahmad Osama SSCertifiable Points: 7676 More actions · Answer 7

GilaMonster (4/17/2008)
Ahmad Osama (4/17/2008)
Well..I think that we cant use CASE statement in update query..however,
Why not?
CREATE TABLE [dbo].[TheTable](
[ID] [int] identity,
[TheName] [varchar](50) NULL
)
GO
insert into TheTable (TheName) Values ('Tom')
insert into TheTable (TheName) Values ('Sam')
UPDATE dbo.TheTable SET TheName = CASE thename WHEN 'Tom' THEN 'Sam' WHEN 'Sam' THEN 'Tom' End

I got this question in a microsoft interview ...I gave the samw update query but was told that I cant use CASE in update statement.

Regards,
[font="Verdana"]Sqlfrenzy[/font]

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

Then the interviewer is either wrong (the syntax is valid and works fine in sQL 2005) or he has something else in mind.

If someone told me that, I would ask why I cannot use a case in an update.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

goodguy SSCertifiable Points: 5456 More actions · Answer 9

What about [Code]UPDATE inventory SET iLease = ABS(iLease - 1)[/Code]

Regards,

goodguy

Experience is a bad teacher whose exams precede its lessons

Anipaul SSC-Insane Points: 24681 More actions · Answer 10

GilaMonster (4/17/2008)
Then the interviewer is either wrong (the syntax is valid and works fine in sQL 2005) or he has something else in mind.
If someone told me that, I would ask why I cannot use a case in an update.

Yeah I agree with Gail. But according to me you lost a golden opportunity to hit back at the interviewer. But at the same time he may wanted to see the confidence in you. Sometimes we also deliberately say a wrong statement to check whether they know or not. 🙂

Ahmad Osama SSCertifiable Points: 7676 More actions · Answer 11

goodguy (4/17/2008)
What about [Code]UPDATE inventory SET iLease = ABS(iLease - 1)[/Code]

gud one:)

Regards,
[font="Verdana"]Sqlfrenzy[/font]

Ahmad Osama SSCertifiable Points: 7676 More actions · Answer 12

Anirban Paul (4/17/2008)
GilaMonster (4/17/2008)
Then the interviewer is either wrong (the syntax is valid and works fine in sQL 2005) or he has something else in mind.
If someone told me that, I would ask why I cannot use a case in an update.
Yeah I agree with Gail. But according to me you lost a golden opportunity to hit back at the interviewer. But at the same time he may wanted to see the confidence in you. Sometimes we also deliberately say a wrong statement to check whether they know or not. 🙂

well..I dint even get time to probe into the question as I was bombarded by 3-4 more questions from the others.....

Regards,
[font="Verdana"]Sqlfrenzy[/font]