UPDATE With Case

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)
LVL 1
tim_carterAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
mfhorizonConnect With a Mentor Commented:
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
 
tim_carterAuthor Commented:
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
 
mfhorizonCommented:
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
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
mfhorizonCommented:
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
 
mfhorizonCommented:
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
 
tim_carterAuthor Commented:
so i cant use two case expressions in one update?
0
 
tim_carterAuthor Commented:
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
 
lwadwellCommented:
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
 
tim_carterAuthor Commented:
It doesnt work, Query Analyzer says. just incorrect syntes near '='
0
 
tim_carterAuthor Commented:
mfhorizon Your statement fails at IF
0
 
tim_carterAuthor Commented:
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
 
tim_carterAuthor Commented:
ups, and in the select before i ofcourse had in the WHERE clause.. AND IsRead IS NULL
0
 
mfhorizonCommented:
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
 
tim_carterAuthor Commented:
mfhorizon. but why is my update statement failing then?
0
 
mfhorizonCommented:
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
 
tim_carterAuthor Commented:
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
 
mfhorizonCommented:
Does above query satissfy your needs?
0
 
tim_carterAuthor Commented:
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
 
mfhorizonCommented:
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
 
bleach77Commented:
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
 
bleach77Commented:
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
 
bleach77Commented:
Lol.. Too late I guess. Cheers :)
0
All Courses

From novice to tech pro — start learning today.