--- rmck <[EMAIL PROTECTED]> wrote:
> mysql> SELECT start,ID FROM table ORDER BY id DESC
> LIMIT 1;
>
> +------------+-----------+
> | start | ID |
> +------------+-----------+
> | 1072603517 | 617168732 |
> +------------+-----------+
> 1 row in set (0.01 sec)
>
> mysql>
>
> So now I want to run my update statment, how do I
> not update the times that have been converted???
> Help....
>
> my current update statement:
> from script:
>
> OID="`echo "select ID from $TBLE order by ID desc
> limit 1;" | $MSQL -u$UI -p$PD -h$HT $DB|grep -v ID`"
> echo "update $TBLE set start = from_unixtime(start)
> where ID > '$OID';"| $MSQL -u$UI -p$PD -h$HT $DB
>
for starters, when u use desc to get ur 'oid', it's
already the highest. so when u say "where ID >
'$OID'", it won't match any rows. '>=' would've
atleast matched that one row. unless BOTH the above
command run repeatedly after changing the highest
value (so then onto the next highest, etc.)
in the manual, mysql> SELECT FROM_UNIXTIME(875996580);
-> '1997-10-04 22:23:00'
so that part is fine.
2 (low tech) solutions that would work *well* are:
1. check the length of the timestamps. atleast so far,
i don't think the no. of seconds would've crossed 19
chars which is the size according the date format
returned by FROM_UNIXTIME (when it's without +0). so u
can use "where length(start) < 19" and u'd only need
the second command.
2. to be y10k compliant :P do an instr search. since u
using that style, there will always be a - or a :
(search for : ... just in case some warped -ve values
got in there). so use "where instr(start, ':')"
i don't see the point about worrying about the ids. in
case some rows were edited in between and the initial
query wasn't run with an "order by" clause, there
could be some in between that haven't been
reformatted. those two solutions are pretty much
foolproof. since some values have already been
converted, i think u've made it a varchar(19) or
something, i.e. it's length is fine.
put semi-colons at the end of my 'where' segments. i
guess they're required.
of course, these queries will run slower coz u're
doing text search stuff rather than a simple id
comparison.
abs
________________________________________________________________________
Yahoo! Messenger - Communicate instantly..."Ping"
your friends today! Download Messenger Now
http://uk.messenger.yahoo.com/download/index.html
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
