Solved

UPDATE With Case

Posted on 2009-04-09
22
272 Views
Last Modified: 2012-05-06
Hi Guys

Can someone tell me what is wrong in this statement?

UPDATE
      MessengerConversations
SET
      ReadBy1 = CASE WHEN (ReadBy1 IS NULL) THEN 1 END,
      ReadBy2 = CASE WHEN (ReadBy1 IS NOT NULL AND ReadBy2 IS NULL AND ReadBy1<>1) THEN ReadBy2=1 END
WHERE
      (FromUserId = 1 OR ToUserId = 1)
      AND (FromUserId = 15 OR ToUserId = 15)
0
Comment
Question by:tim_carter
  • 10
  • 8
  • 3
  • +1
22 Comments
 
LVL 1

Author Comment

by:tim_carter
ID: 24105909
UPDATE
      MessengerConversations
SET
      ReadBy1 = CASE WHEN (ReadBy1 IS NULL) THEN 1 ELSE ReadBy1 END,
      ReadBy2 = CASE WHEN (ReadBy1 IS NOT NULL AND ReadBy2 IS NULL AND ReadBy1<>1) THEN ReadBy2=1 ELSE ReadBy2 END
WHERE
      (FromUserId = 1 OR ToUserId = 1)
      AND (FromUserId = 15 OR ToUserId = 15)

Sorry this should be right, but doesnt work
0
 
LVL 5

Expert Comment

by:mfhorizon
ID: 24105919
Case doens't access logical operation,
correct should be

UPDATE
      MessengerConversations
SET
      ReadBy1 = (CASE ReadBy1 WHEN NULL THEN 1 ELSE ReadyBy1 END),
WHERE
      (FromUserId = 1 OR ToUserId = 1)
      AND (FromUserId = 15 OR ToUserId = 15)
0
 
LVL 5

Expert Comment

by:mfhorizon
ID: 24105956
UPDATE
      MessengerConversations
SET
      ReadBy1 = (if ReadBy1 IS NULL select 1 else selcet ReadBy1),
      ReadBy2 = (if ReadBy1 IS NOT NULL AND ReadBy2 IS NULL AND ReadBy1<>1 select 1 else ReadyBy2)
WHERE
      (FromUserId = 1 OR ToUserId = 1)
      AND (FromUserId = 15 OR ToUserId = 15)
0
 
LVL 5

Expert Comment

by:mfhorizon
ID: 24105986
if you read SQL help for Case Sytax it will tell that i it used only when you need to compare single values (like menu options) and set / return different value for every option. Every comparision in Case syntax is specified by WHEN and every reutrn value is specified by THEN. Case is ended by END.

If you need to compare two values at a time using logical operation  <>, =, <, >, etc. you must use If else structure (where THEN is not used), as mentioned in my above query. This query should run perfectly.

Cheers!
0
 
LVL 1

Author Comment

by:tim_carter
ID: 24106004
so i cant use two case expressions in one update?
0
 
LVL 1

Author Comment

by:tim_carter
ID: 24106014
then what would be the correct way of doing it. I want to update one of the fields, either, ReadBy1, OR ReadBy2. It is so both parties in the conversation will read the message
0
 
LVL 25

Expert Comment

by:lwadwell
ID: 24106023
Hi tim_carter,

Why do you say 'it doesn't work" ... are you getting an error or is it just not updating what you expect it to update?


lwadwell
0
 
LVL 1

Author Comment

by:tim_carter
ID: 24106035
It doesnt work, Query Analyzer says. just incorrect syntes near '='
0
 
LVL 1

Author Comment

by:tim_carter
ID: 24106038
mfhorizon Your statement fails at IF
0
 
LVL 1

Author Comment

by:tim_carter
ID: 24106053
OK Guys. You probably have a better solution. What it is for is for a messenger im building. Where both parties of a conversation need to read messages they have not read yet.

I get the messages like this

SELECT
                  t1.MessageId,
                  convert(char,t1.CreatedDate,108) CreatedDate,
                  t1.FromUserId,
                  t2.UserName FromUserName,
                  t1.ToUserId,
                  t3.UserName ToUserName,
                  t1.Text
            FROM
                  MessengerConversations t1
            LEFT JOIN Users t2 ON t1.FromUserId=t2.UserId
            LEFT JOIN Users t3 ON t1.ToUserId=t3.UserId
            WHERE
                  (t1.FromUserId=".$_SESSION['UserId']." OR t1.ToUserId=".$_SESSION['UserId'].")
                  AND (t1.FromUserId=$UserId OR t1.ToUserId=$UserId)
            ORDER BY
                  t1.CreatedDate ASC


now after this i want to update the messages the person has gotten from the table, because i dont want him to get them again. But the other person still needs to get them . but of course only once.

i did like this after my select

UPDATE MessengerConversations SET IsRead = 1 WHERE (FromUserId = ".$_SESSION['UserId']." OR ToUserId = ".$_SESSION['UserId'].") AND (FromUserId = $UserId OR ToUserId = $UserId)

but ofcourse it only works one way, and means that the first person getting the messages also sets, isread = 1, and then the other person cant get them..

Any one have a good idea, on how to solve this? All help is much appreciated.
0
 
LVL 1

Author Comment

by:tim_carter
ID: 24106057
ups, and in the select before i ofcourse had in the WHERE clause.. AND IsRead IS NULL
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 5

Expert Comment

by:mfhorizon
ID: 24106082
Yes, i checked in sql server now, if is not allowed in update statement. HOwever you can use two case statement in on update clause it is allowed.
0
 
LVL 1

Author Comment

by:tim_carter
ID: 24106089
mfhorizon. but why is my update statement failing then?
0
 
LVL 5

Accepted Solution

by:
mfhorizon earned 500 total points
ID: 24106091
Try this


UPDATE
      MessengerConversations
SET
      ReadyBy1 = isnull(ReadyBy1,1),
      ReadyBy2 = (case isnull(ReadyBy1,0) WHEN 0 THEN ReadyBy2 ELSE isnull(ReadyBy2,1) END)
WHERE
      (FromUserId = 1 OR ToUserId = 1)
      AND (FromUserId = 15 OR ToUserId = 15)
0
 
LVL 5

Expert Comment

by:mfhorizon
ID: 24106105
Actually in above query lots of conditions are handled by isnull() function itself.

Let me read out above query.

update ReadyBy1 as 1 when ReadyBy1 is null - else update ReadyBy1 as ReadyBy1 (whatever Readyby1 is)

update ReadyBuy2 as ReadyBy2 (whatever Readyby2 is) when ReayBy1 is Null - else update ReadyBy2 as 1 if ReadyBy1 is not null and ReadyBy2 is null.

Enjoy!
0
 
LVL 1

Author Comment

by:tim_carter
ID: 24106200
Thats now exactly what i want, you se i only want to update IsRead1 if it has no value, and IsRead2 if IsRead1 has a value, that is not the $_SESSION['UserId']

that way the other user will update IsRead2 if IsRead1 is updated by the first user, and none of them will read that message again, makes sense?
0
 
LVL 5

Expert Comment

by:mfhorizon
ID: 24106210
Does above query satissfy your needs?
0
 
LVL 1

Author Comment

by:tim_carter
ID: 24106230
This actually works


UPDATE
      MessengerConversations
SET
      ReadBy1 = isnull(ReadBy1,2),
      ReadBy2 = (case WHEN ReadBy1 IS NOT NULL AND ReadBy1<>2 THEN 2 ELSE ReadBy2 END)
WHERE
      (FromUserId = 1 OR ToUserId = 1)
      AND (FromUserId = 15 OR ToUserId = 15)

Thanks alot
0
 
LVL 5

Expert Comment

by:mfhorizon
ID: 24106244
Thanks tim,

I wish if you could have accepted solution for the right query, it would be easy for other users to have a quick look on solution.

Good luck!
0
 
LVL 4

Expert Comment

by:bleach77
ID: 24106261
Just about this ignore this case since I don't have SQL query in this machine, but I realize your code have something weird.
UPDATE

      MessengerConversations

SET

      ReadBy1 = CASE WHEN (ReadBy1 IS NULL) THEN 1 ELSE ReadBy1 END,

      ReadBy2 = CASE WHEN (ReadBy1 IS NOT NULL AND ReadBy2 IS NULL AND ReadBy1<>1) THEN ReadBy2=1 ELSE ReadBy2 END

                    ^------- Here (Should be 1 instead ReadBy2=1)

WHERE

      (FromUserId = 1 OR ToUserId = 1)

      AND (FromUserId = 15 OR ToUserId = 15)

Open in new window

0
 
LVL 4

Expert Comment

by:bleach77
ID: 24106268
Woops.. Cut off text.. You should get it though
THEN ReadBy2=1 ELSE ReadBy2 END

        ^------- Here (Should be 1 instead ReadBy2=1)

Open in new window

0
 
LVL 4

Expert Comment

by:bleach77
ID: 24106282
Lol.. Too late I guess. Cheers :)
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now