CODEWITHSUNDEEP
sql
Nektarios
Nektarios

Reputation: 10371

SQL update to a table based on a flag word?

I've got a field in my DB that's an arbitrary value on a per-row basis, and I'd like to add X to this. I'd only like to add X if a flag word (held as an int in this row) has the 2nd and 10th bits set true. Is it possible to create an SQL statement to do this for every row in the table? Or do I have to iterate through my entire table?

Using MySQL (5.5)

Bonus points question: I say add X based on a flag, but there's also a scaling factor. For example, based on a value of bits 20-12 interpreted as a short unsigned integer, I'd really like to assign:

value = value + ('X' * thatShort * (bit2 and bit10));

Upvotes: 1

Views: 101

Answers (1)

Alex Aza
Alex Aza

Reputation: 78467

In MS SQL:

update MyTable
set Field1 = Field1 + 'X'
where Field2 & 0x202 = 0x202

[EDIT]

value = value (X * (field & 0x1FF800 >> 12) * 0x202)

0x1FF800 - is the mask from 12 to 20.
>> 11 - shift it to remove bits from 0 to 12.
since you are filtering by bit2 and bit10 set, then (bit2 and bit10) = 0x202

Hope this will answer your question. Not sure how you are going to grant 'bonus points' though :).

Upvotes: 3

Related Questions