- Possibly use a stored procedure

- Triggers, as implemented in MySQL can't do this

Did you really mean "row", or did you mean "column"?

I'd first re-visit your schema. It sounds like you are trying to implement a mutually-exclusive choice. Something tells me you've modeled this "inside out". That is, perhaps your columns should be rows, and your rows should be columns... Or perhaps you need an additional relational table, or the selected item should be referenced from some other table.

You've got a bunch of rows, only one of them can have a "1" in a certain column. The rest of the rows need to have a "0" there. Isn't there some other place where you can store the choice? (In another table?)

Without a more concrete example, it's hard to give further advice. Let's see if we can make one.

Let's say a customer has multiple potential shipping addresses. Only one is their "current" shipping address, though. So, you have a "current" column, set to 1 for the single current shipping address.

I wouldn't do this. Instead, I'd stick the primary key of the current shipping address in the customer record.

the way i have it now, is i have a column named 'current'.

the primary key is an index key which is an autoincrement integer.

I'm not sure about your particular implementation, but if each customer for instance could have many rows and but only one is current for a given customer, then rather than having something like:

Table: CustomerOption

with 3 columns

CustomerID OptionID Current
123 1 0
123 2 0
123 3 1
456 1 0
456 2 0
456 3 0
456 4 1
456 5 0

you may want to have 2 tables like:

Table1: CustomerOption

with 2 columns

CustomerID OptionID
123 1
123 2
123 3
456 1
456 2
456 3
456 4
456 5

and a separate table to store the current option for each customer such as:

Table2: CustomerCurrentOption

with 2 columns

CustomerID CurrentOptionID
123 3
456 4

This way you only have to update a single row when current changes. Also you can save a lot of unnecessary space in the database not storing a bunch of unnecessary 0's for non-current rows.

You can create a VIEW of the two tables using a LEFT OUTER join to give the appearance that it is still one table if you'd like. If the left joined column is NULL return a 0 otherwise if there is a matching row in CustomerCurrentOption (i.e. the left joined column is NOT null) return a 1.

Just a thought.

[edited by: ZydoSEO at 10:25 pm (utc) on Dec. 30, 2007]