MySQL - auto-incrementing a field

Possible without a read first?

[trillianjedi]

I've been getting stuck in to MySQL lately, something I usually outsource, but I fancy having a go and learning a few things. I'm doing ok but I've got stuck and suspect you guru's might have the answer.

I have a two (largeint) fields in a DB called "in" and "out" and I need to either increase or decrease them by 1 depending on a particular status.

Is there an easy way in MySQL, that avoids me having to read in the value, do the math, then write it back? I'd like to just say "increment `in` by 1", "decriment `out` by 1" etc.

Always going up or down in 1's.

I've tried my favourite search engine, but I keep finding lots of info about "auto-incrementing" fields which, from what I've read, won't help me.

Any input appreciated.

TJ

[s1dev]

update tablename set in = in+1 ...

[trillianjedi]

That easy eh?

How stupid do I feel!

Many thanks!

TJ

[freeflight2]

also if you define a table with an auto_increment ID this ID will automatically increment with each INSERT

[trillianjedi]

Thanks freeflight, but I need to increment fields at my discretion, not every insert (I did find the help on auto-increment fields, but it doesn't quite do what I want).

s1dev's solution is what I needed. Out of interest, does this work with float values?

set `some_float` = `some_float + 0.47`

Thanks,

TJ

[freeflight2]

yep works with all types of fields, even with varchar e.g.:
UPDATE table SET field=CONCAT(field, 'trillianjedi');
to modify a string.

[trillianjedi]

Fantastic, I'm starting to really like working with SQL.

All new to me, but quite straightforward really.

TJ

[freeflight2]

really like working with SQL.

the beauty about SQL is that once you've set up table schemas and relations between tables writing the application is often trivial.