#mysql
11 October 2007
Total 31 pages. You are browsing page 2/31.
First :: Prev :: [1] [2] [3] [4] [5] [...] :: Next :: Last
01:02 <****> I'm hoping to find all ids in table_a where table_b_id is null, then do an insert into table_b, and then update table_a's table_b_id field with the result of last_insert_id()
01:10 <****> i have a table with a few datetime fields and want to convert them to timestamp
01:11 <****> will i lose data if i just change the type?
01:12 <****> I want to fetch ssn from a table with condition that the ssn's are not in the form XXXXXX-XXXX (X stands for any number 0-9). How can I tell mysql this condition?
01:13 <****> rlike
01:13 <****> mEck0: what format are they in?
01:13 <****> sickdm, according to http://dev.mysql.com/doc/refman/5.0/en/timestamp.html , both datetime and timestamp has the same format
01:15 <****> http://dpaste.com/22003/
01:15 <****> Annihilation, the ssn's are saved as XXXXXX-XXXX (for instance 880122-1111). The final query should result in empty set I think. Not so useful, but it's a task which must be done...
01:16 <****> mEck0: I'm not understanding your question. What is it that you need to accomplish, exactly?
01:17 <****> mEck0: i was worried if there wasn an issue, one is a 4 byte int (ts), another is a 8byte thing i think
01:17 <****> Annihilation, "Collect the name and address of the employees that have an ssn that is not in the form XXXXXX-XXXX where X is a number between 0 och 9."
01:17 <****> sickdm, ah, don't know
01:19 <****> Annihilation, because there is a person with an ssn including an B instead of a number 0-9, the query should show that person as a result (this wrong ssn is a deliberate for exercise purpose)
01:21 <****> mEck0: I understand now. Give me just a moment, I'm working on it. Ideally though, you should prevent bad data from getting into the table in the first place.
01:21 <****> and is it 123456-1234 or 12345-1234
01:21 <****> Annihilation, 123456-1234
01:21 <****> thats the right form
01:21 <****> are these US ssns? because they're usually 123-12-1234
01:22 <****> no, swedish ssn's
01:22 <****> gotcha
01:22 <****> alright, hang on
01:23 <****> yeah, thats true, but I made the ssn-field of type char and added the "bad" ssn because it was another exercise for us...
01:24 <****> mEck0 try: "SELECT * FROM employee WHERE ssn REGEXP '[0-9]{6}-[0-9]{4}'"
01:24 <****> ok
01:25 <****> er
01:25 <****> that'll bring back everything that's right
01:25 <****> yeah, that seems to work :) thx a lot!
01:25 <****> that DID work?
01:25 <****> yeah
01:26 <****> ohh wait a minute
01:26 <****> it's bringing back stuff that's in the right format, isn't it
01:26 <****> 123456-1234
01:26 <****> instead of 12345A-1234
01:27 <****> yeah, thats right, the query shows me the right ssn's which contains only numbers
01:27 <****> mEck0 try this: "SELECT * FROM employee WHERE ssn REGEXP '^[[0-9]{6}-[0-9]{4}]'"
01:27 <****> *trying*
01:29 <****> $
01:30 <****> Annihilation, hmm, got Empty set as a result
01:31 <****> yeah, that's what I'm getting too. my regular expressions aren't to great - i always have trouble negating them.
01:31 <****> still working
01:31 <****> I have an ssn 11223B-1122 in the Employee table, this is the one which should be written out
01:31 <****> ahh, ok
01:31 <****> PHP sucks. Going to instaban anyone who talks about it here..........
01:31 <****> so tempting
01:32 <****> seekwill: yay!
01:32 <****> SELECT * FROM ssn WHERE ssn REGEXP '[0-9]{6}-[0-9]{4}'
01:32 <****> :P
01:32 <****> will return those that are right.
01:32 <****> now to figure out how to make it return those that are wrong
01:33 <****> can't we use NOT LIKE ssn REGEXP....?
01:33 <****> I mean, SELECT * FROM ssn WHERE ssn NOT LIKE REGEXP '[0-9]{6}-[0-9]{4}' ?
01:33 <****> no clue - give it a whack
01:34 <****> got sql syntax error
01:35 <****> mEck0: how about: select from table where id not in (SELECT id wehre something matche regexp) ????
01:35 <****> caotic, *trying*
01:36 <****> NOT REGEXP
01:36 <****> ok
01:36 <****> SELECT * FROM ssn WHERE ssn NOT REGEXP '[0-9]{6}-[0-9]{4}'
01:36 <****> will do it
01:37 <****> Annihilation, yeah! great, not regexp worked :) thx a lot
01:37 <****> My pleasure :)
01:37 <****> That'll be seven million dollars please :)
01:37 <****> ;)
01:38 <****> Annihilation, I have some more queries which I can't figure out how to solve, if you want to help me more?
01:38 <****> post away, I'll help with whatever I can :)
01:38 <****> ok, nice
01:38 <****> "Collect the total sum of all debts for all gambling the last week."
01:39 <****> I got an syntax error, w8 a bit, *writing down query*
01:40 <****> select sum(debt) from GamingInstance (select datediff('2007-10-10','2007-10-03'));
01:40 <****> hello
01:40 <****> why cant i use where instead having in this example? http://www.sqlcommands.net/sql+having/
01:42 <****> 'mm/dd/yy' and date < 'mm/dd/yy'
01:42 <****> Annihilation, ok, will give it a try
01:43 <****> I have a table t, but when i do CREATE VIEW t.v AS SELECT qty, price, qty*price AS value FROM t; I get ERROR 1142 (42000): CREATE VIEW command denied to user 'databases'@'localhost' for table 'v' what's wrong?
01:44 <****> the user you're connected as doesn't have create privs on the table/db
01:45 <****> I granted all privileges, and when I do it as root, I get: ERROR 1 (HY000): Can't create/write to file './t/v.frm~' (Errcode: 2)
01:47 <****> Annihilation, it works :) maybe it's not as fine as the datetime() but it works :)
01:47 <****> lgbr: view can't match table name
01:47 <****> ie
01:47 <****> table t, view can't be t.v
01:47 <****> oh
01:47 <****> SHOW PROCESSLIST shows, Binlog Dump: Has sent all binlog to slave; waiting for binlog to be updated
01:48 <****> any way to add a calculated field to a table?
01:48 <****> but my slave has not updated to match the master
01:49 <****> lgbr: you mean you have three columns, qty, price, and value, and you want to run a query that will calculate and store value based on qty and price?
01:49 <****> yes
01:49 <****> Annihilation, just solved it with datediff(), select sum(debt) from GamingInstance where datediff('2001-11-01','2001-11-07');
01:50 <****> mEck0: that was another suggestion
01:50 <****> the subquery was kind of overkill is the point i was making :)
01:50 <****> hehe
01:50 <****> Everyone, /version chadmaynard
01:51 <****> lgbr: you can TRY "UPDATE table SET value=price*qty
01:51 <****> but i don't know if that'll work - and it MIGHT screw up your data
01:51 <****> do it in a test instance first.
01:53 <****> it works, but it's not automatic
01:53 <****> when do you want it to happen?
01:53 <****> when the data is read
01:53 <****> should i attempt to rebind the slave to the master?
01:54 <****> select price, qty, (price*qty) as value ??
Total 31 pages. You are browsing page 2/31.
First :: Prev :: [1] [2] [3] [4] [5] [...] :: Next :: Last