SQL issue - need to script a fix

Easy way out of a corrupt table?

[trillianjedi]

Hi guys,

I screwed up an application and now have corrupt data in a table. It's not a huge deal, but a manual fix at this stage would mean me going through about 1,200 records and hand editing ;-)

I think I can automate this, as all the data is there, it's just sometimes the wrong value appears in a field.

I have a table with the following fields:-

user
seconds
debit

In all of the corrupt records, debit is -1 (the result of me making an error in a function return -1 then forgetting to deal with the error, instead adding -1 to the debit field - DOH!).

So, eg, I might have the following record as one of the faulty ones:-

user      seconds  debit
   
trillianjedi  152    -1.00

The true value of what is currently -1 in the debit field is really easy to obtain - it's seconds * x (where x is a float value and constant).

Is there anyway I can build something to fix this automagically?

In rough logic language, it would be :-

Return all fields where debit = -1
Update each debit field to (seconds * x)

Loop to finish.

Can I do that in a single SQL statement, or would it be easier to build a php script to call from the command line to do it?

This is a one-off - the main app producing the defective data is now repaired.

Many thanks!

TJ

[ergophobe]

UPDATE mytable SET debit=(seconds * 3.14159) WHERE debit = '-1';

[mcibor]

UPDATE table SET debit=seconds * 2.354 WHERE debit='-1';

This should work.
Michal Cibor

Use Ergophobe's solution - he probably tested it, I just took it out of blue

[trillianjedi]

Man that saved me a lot of typing ;-)

Thanks you....

TJ

[ergophobe]

Actually, the first thing I wrote was

UPDATE mytable SET debit=(debit * 3.14159) WHERE debit='-1';

So I decided my brain was off and I'd better test. So I did. They all work BTW, with or without parens, with or without quotes around the '-1'.

[trillianjedi]

I used:-

UPDATE mytable SET `debit = (`debit` * 3.14159) WHERE debit='-1';

Worked a treat.

Thanks again.

TJ

[ergophobe]

No you didn't. Couldn't have ;-)

[trillianjedi]

Serves me right for copy/pasting your mistaken example and being lazy ;-)

Same as above but with seconds in it....