#mysql
02 November 2007
Total 16 pages. You are browsing page 3/16.
First :: Prev :: [1] [2] [3] [4] [5] [...] :: Next :: Last
04:21 <****> cause i try all the tools to repair the table and still fail
04:21 <****> shido6: Define "no worky".
04:22 <****> If I have a table with an arbitrary number of fields. Where the value of field `foo` should be unique for any given value of the field `bar`. Is there anyway to enforce this in the table structure. (As one would with a UNIQUE field if we didn't have the dependency on `bar`.)
04:22 <****> update mya2billing . cc_card set tariff = 1;
04:22 <****> Query OK, 496 rows affected (0.14 sec)
04:22 <****> Rows matched: 500 Changed: 496 Warnings: 0
04:22 <****> dacm: you can create a unique index on those two fields
04:22 <****> woo hoo!
04:23 <****> den i found an article regarding the move the myi and frm file from a new table and put in the crash table and try repair it.
04:23 <****> so when i try to replace the frm file in mysql i getting read only error
04:23 <****> thank you!
04:24 <****> jbalint: Ah great. Just wanted to understand if it was actually possible. This does mean that I will finally have to get to grips with indexes (indices?) though! :-)
04:26 <****> any idea?
04:47 <****> I have this monster query, and I need to turn it into an UPDATE statement... to delete the results returned from the query
04:47 <****> SELECT u.id, u.last_visit FROM mantis_user_table u LEFT JOIN mantis_bug_table b ON u.id = b.reporter_id LEFT JOIN mantis_bugnote_table c on u.id = c.reporter_id WHERE b.reporter_id IS NULL AND c.reporter_id IS NULL AND u.last_visit LIKE '%2006%';
04:48 <****> DELETE FROM .. WHERE col IN (SELECT ...) ?
04:49 <****> delete mantis_user_table from ....
04:49 <****> copy the rest from the select
04:49 <****> this will delete rows, not update
04:49 <****> kimseong: yeah, DELETE works on a join too
04:50 <****> DELETE FROM mantis_user_table WHERE SELECT u.id, u.last_visit FROM mantis_user_table u LEFT JOIN mantis_bug_table b ON u.id = b.reporter_id LEFT JOIN mantis_bugnote_table c on u.id = c.reporter_id WHERE b.reporter_id IS NULL AND c.reporter_id IS NULL AND u.last_visit LIKE '%2005%';
04:50 <****> no
04:50 <****> delete mantis_user_table from ... copy the rest after the from
04:50 <****> after the from in your select
04:51 <****> DELETE mantis_user_table FROM mantis_user_table u LEFT JOIN mantis_bug_table b ON u.id = b.reporter_id LEFT JOIN mantis_bugnote_table c on u.id = c.reporter_id WHERE b.reporter_id IS NULL AND c.reporter_id IS NULL AND u.last_visit LIKE '%2005%';
04:51 <****> But doesn't that skip the initial select?
04:51 <****> this delete the whole rows from the table
04:52 <****> not 2 value in the table
04:52 <****> This doesn't seem to work
04:52 <****> ERROR 1109 (42S02): Unknown table 'mantis_user_table' in MULTI DELETE
04:52 <****> setuid: Search for "multiple tables" in http://dev.mysql.com/doc/refman/5.1/en/delete.html
04:52 <****> I don't want to delete the table, I want to delete the matching rows
04:52 <****> yes, the matching rows
04:53 <****> try delete u from ...
04:53 <****> nope, syntax errors
04:55 <****> or remove the u alias
04:56 <****> DELETE FROM u USING mantis_user_table u LEFT JOIN mantis_bug_table b ON u.id = b.reporter_id LEFT JOIN mantis_bugnote_table c on u.id = c.reporter_id WHERE b.reporter_id IS NULL AND c.reporter_id IS NULL AND u.last_visit LIKE '%2006%';
04:56 <****> That works
04:57 <****> anyone out there
04:57 <****> hopefully it delete what you really wanted to, not more
04:58 <****> kimseong, It did
04:59 <****> a complicated delete is quite dangerous, a mistake can cause loss of data
05:02 <****> I backed up everything first, of course
05:13 <****> I am trying to force a number onto my articles if a duplicate is found. but I wish to not change the existing artticle name.
05:13 <****> INSERT INTO foo (articles) VALUES ('blah-blah') ON DUPLICATE KEY UPDATE articles=CONCAT(articles, foo_id);
05:13 <****> Shai_Tan, INSERT IGNORE
05:15 <****> INSERT INGNORE INTO foo (articles) VALUES ('blah-blah') ON DUPLICATE KEY UPDATE articles=CONCAT(articles, foo_id);
05:15 <****> like that?
05:16 <****> ERROR 1064 (42000): You have an error in your SQL syntax
05:20 <****> with DBI::mysql, is there "select" command
05:25 <****> so, anyway, I need to keep the article names unique, so wish to append either a number or the id when I INSERT
05:25 <****> is this doable?
05:46 <****> hello
05:46 <****> can you use innoDB tables and myISAM tables together?
05:47 <****> like do a query on both table types?
05:49 <****> mudge: sure
05:51 <****> okay, but you can't do a transaction with a myISAM table involved right?
05:51 <****> or can you?
05:51 <****> mudge: MyISAM doesn't do transactions
05:52 <****> k, thanks
05:52 <****> what do you like using, innodb or MyISAM?
05:52 <****> Does my preference matter?
05:52 <****> well do you have experience and/or studied mysql?
05:53 <****> yes
05:53 <****> it probably matters then
05:53 <****> to me
05:54 <****> I use InnoDB whenever I can
05:54 <****> cool, because of its reliability and transactions?
05:55 <****> hay mysql guise
05:56 <****> mudge: transactions, crash recovery, hot backup, foreign keys...
05:58 <****> ah cool, does the foreign keys thing mean, that when you delete a row, that other rows with a key the row you deleted also gets deleted?
05:58 <****> mudge: up to you. either delete in the other table, set values to NULL, or reject the query
06:00 <****> wow, cool, so you can set it to do one of those various things
06:30 <****> did anyone's mysql break after installing ubuntu gutsy?
06:30 <****> I keep getting MYSQL ERROR when trying to connect via remote clients. There are no firewalls on that I am aware of.
07:01 <****> hi,how to add 1 day to a date field ?
07:01 <****> ie to find next day of 2007-05-25
07:02 <****> echo somecoolfunction(2007-05-25); //prints 2007-05-26
07:06 <****> power: SELECT dateCol + INTERVAL 1 DAY as dateCol FROM table;
07:07 <****> thanks
07:07 <****> ... update date='date + INTERVAL 1 DAY' where ....
07:07 <****> ops
07:08 <****> update date='$somedate + INTERVAL 1 DAY'
07:08 <****> does that also work chadmaynard?
07:08 <****> i mean set date....
07:08 <****> you want to update the dates in the table?
07:09 <****> UPDATE table SET dateCol = dateCol + INTERVAL 1 DAY;
07:09 <****> changes every date in dateCol to the next day
07:11 <****> ok thanks
07:12 <****> set start_date='2007-10-31 + INTERVAL 1 DAY'
07:12 <****> that query updated it to 2007-10-31
07:12 <****> not to 2007-11-01
07:15 <****> power: that's because you used it as a string
07:15 <****> SELECT '2007-10-31' + INTERVAL 1 DAY;
07:15 <****> you did this: SELECT '2007-10-31 + INTERVAL 1 DAY';
07:20 <****> so how do i write it in php
07:20 <****> query="update table set startdate= "; ?
07:20 * thumbs smashes chadmaynard, in a tender, loving way
07:21 <****> power: i don't know what you are doing. you have the date in a variable?
07:21 <****> yes ,say $date
Total 16 pages. You are browsing page 3/16.
First :: Prev :: [1] [2] [3] [4] [5] [...] :: Next :: Last