Update syntax

[Saeven]

Hello, I have a table with four fields, three of which are integers.

The fields are called for instance, 'letter', 'n1', 'n2', 'n3'

Let's say that my table set looks like this:

A 1 2 3
B 2 3 4
C 3 4 5

I require an operation that updates any number in all columns to be a new number. Example, set 3 to zero and we'd get:

A 1 2 0
B 2 0 4
C 0 4 5

I know how to do this with three UPDATE statements. I'm sure it can be done in a singular statement. Could someone share 🙂

Thanks in advance!

[ultraslacker]

Originally posted by Saeven
I'm sure it can be done in a singular statement.

No it cannot - when you have a denormalized design, you will run into problems like this.

[nnichols]

You could do it using -

UPDATE table SET n1 = IF(n1=3, 0, n1), n2 = IF(n2=3, 0, n2), n3 = IF(n3=3, 0, n3);

but as ultraslacker said you should probably be looking at your overall design.

[Saeven]

You guys are too quick to dismiss design - it is used to store persistent data as pointers to elements. When the element is deleted, its indices are removed.