Solved

UPDATE With Case

Posted on 2009-04-09
22
307 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
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

Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Use Distinct with two fields 3 21
Select question from MySQL 1 20
SQL Rewrite without the NULLIF 4 25
Help with Progress 4gl Rounding Function 6 28
In database programming, custom sort order seems to be necessary quite often, at least in my experience and time here at EE. Within the realm of custom sorting is the sorting of numbers and text independently (i.e., treating the numbers as number…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

726 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