Tinyint bitwise 'or' on an update ?

[120]

I've spent the afternoon googling for something that should be easy to do, but I can't get to work with a simple query. It may be a limitiation of MySQL, it may be my syntax so I figure it's best to ask the experts :-)

I'm trying to bitwise OR a tinyint field in an update field but despite the match, the 'OR' does not work :-(

UPDATE stats SET warn_dns=(warn_dns|8) WHERE id=24302;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 0

UPDATE stats SET warn_dns=warn_dns|8 WHERE id=24302;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 0


However, I can do this:

UPDATE stats SET warn_dns=warn_dns+8 WHERE id=24302;
Query OK, 1 rows affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0


I can go around the houses pulling the value into a script, manually 'or'ing it, then updating it back to the database, but I thought there should be an easy way?:confused:

[Fou-Lu]

I guess it depends on the value of warn_dns in the first place. I assumed you know how to handle bitwise values.
If warn_dns is already 8, the result will only be 8. If its say 3, then the result will be 11. If its 15, the result will be 15.
An unsigned tiny int though will only give you a maximum of 9 possible values. Thats not very many, but I guess it depends on what you're using it for.

[Old Pedant]

Actually, I'd consider that to be a very nice feature of MySQL, that it compares the before and after values and, if they are the same, doesn't count that row as "changed".

I'll bet that not all DBs do that. It's not a fair comparison, but I'm sure that Access doesn't, for example. It just assumes that any row matched by the WHERE clause *will* be updated, even if the update doesn't change anything in a given row.

[Old Pedant]

An unsigned tiny int though will only give you a maximum of 9 possible values
Okay, I give up. How are you counting those values????

If you mean there are 9 *independent* bit values, then no, there are 8.

If you are trying to count all possible combinations, then there are 256, of course.

But 9???

You've got me on that one.

[Fou-Lu]

Okay, I give up. How are you counting those values????

If you mean there are 9 *independent* bit values, then no, there are 8.

If you are trying to count all possible combinations, then there are 256, of course.

But 9???

You've got me on that one.

Oops, my bad there. Yeah I meant individual, forgot that 8 bits contains 256 values including the 0, not that it goes up to 256. My bad :o

[koko5]

I'm trying to bitwise OR a tinyint field in an update field but despite the match, the 'OR' does not work :-(

UPDATE stats SET warn_dns=(warn_dns|8) WHERE id=24302;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 0

UPDATE stats SET warn_dns=warn_dns|8 WHERE id=24302;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 0


If warn_dns is null or warn_dns=8 where id=24302:

mysql> SELECT 8|8;
+-----+
| 8|8 |
+-----+
| 8 |
+-----+
1 row in set (0.00 sec)

mysql> SELECT NULL|8;
+--------+
| NULL|8 |
+--------+
| NULL |
+--------+
1 row in set (0.00 sec)
^^Changed 0 rows.Perfect

[120]

To answer the points, no the tinyint was not 8 to start with it had the value of zero. It seems the query just does not work beyond the match, which leads me to think it's a syntax issue.

Select statements are no issue - it's UPDATE statements that refuse to work.

BTW I'm not seeing this as useful to me - am I missing something?
struct User *upFou;
userInit(upFou, "Fou-Lu", 1);
printf("%s has %s to %s\n", (*upFou).Name, !quitSmoking(upFou) ? "FAILED" : "SUCCEEDED", (*upFou).Smoker == 1 ? "FAIL" : "PASS");
// Fou-Lu has FAILED to FAIL? Lol

[koko5]

Select statements are no issue - it's UPDATE statements that refuse to work.
You didn't get my point!

UPDATE stats SET warn_dns=(warn_dns|8) WHERE id=24302;

OK, matching one row, but if warn_dns IS NULL or warn_dns=8 WHERE id=24302 (matching row) setting up warn_dns to the same value, without changes -> 0 changed:

UPDATE stats SET warn_dns=(warn_dns|8)
WHERE id=24302
AND warn_dns IS NOT NULL
AND warn_dns<>8; //to assure changes upon matches

Edit: The same:
mysql> create table testings (id int auto_increment primary key,i int);
Query OK, 0 rows affected (0.00 sec)

mysql> insert into testings values(1,2);
Query OK, 1 row affected (0.00 sec)

mysql> update testings set i=2 where id=1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 0
//because i=2
mysql> update testings set i=1 where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
//changed i from 2 to 1
:)

[Fou-Lu]

BTW I'm not seeing this as useful to me - am I missing something?
struct User *upFou;
userInit(upFou, "Fou-Lu", 1);
printf("%s has %s to %s\n", (*upFou).Name, !quitSmoking(upFou) ? "FAILED" : "SUCCEEDED", (*upFou).Smoker == 1 ? "FAIL" : "PASS");
// Fou-Lu has FAILED to FAIL? Lol

o.O
Thats my signature, not meant to be a part of the code block :D

[Old Pedant]

Koko's suggestions work, but I'm not clear what the point of them are.

I would *assume* that you are going to use this code in some application, and then you won't *see* the separate rows matched and rows changed numbers. So who cares that they don't match when no actual change takes place??

As for the problem with NULL--which 120 says doesn't apply in his case but let's address it for completeness--you could always do:

UPDATE stats SET warn_dns= IFNULL(warn_dns,0) | 8 WHERE id=24302;

[120]

Gentlemen - I sincerely apologise. The field was in fact null after my protestations that it contained '8'. Running the query against the correct field tends to help somewhat :-) Particular thanks to The Old Pedant who's inspiration:

UPDATE stats SET warn_dns= IFNULL(warn_dns,0) | 8 WHERE id=24302;

highlighted and solved the issue - I'm am very much obliged to you sir. Thank you.