#mysql
11 October 2007
Total 31 pages. You are browsing page 5/31.
First :: Prev :: [...] [3] [4] [5] [6] [7] [...] :: Next :: Last
02:54 <****> is it possible to do a if not match then value = 999?
02:55 <****> after my update, change the column to an int type
02:55 <****> then find any rows that are null :)
02:58 <****> Simetrical, see the query you said, do i need to include the products2 table in the join there at all?
03:01 <****> wanderingii, try it without if you like. You can't refer to columns that aren't in any of the tables you specified.
03:01 <****> yea i did btu it didtn work
03:02 <****> I have a table Customer with an attribute SSN, and a table GamingInstance with an attribute customerSsn. I want to list the customers whom have never gambled (i.e. their customerSsn in GamingInstance does not exist, only ther SSN in Customer). I know that I should use EXIST, but got an error in my query.
03:02 <****> select SSN from Customer inner join customerSsn from GamingInstance where SSN=customerSsn not exist (select customerSsn from GamingInstance);
03:03 <****> err your syntax is... well... totally wrong :p
03:03 <****> select ssn from customer left join gaminginstance on ssn = customerssn where customerssn is null
03:05 <****> Using a subquery would be possible too, it would just be slower.
03:05 <****> slower!?!?!?
03:05 <****> Faster?
03:05 <****> You undoubtedly know better.
03:06 <****> oh i just like to come in and repeat the last line with a ? at the end.
03:06 <****> makes people double-guess themselves
03:06 <****> . . . right.
03:06 <****> ignore him! he's just being difficult
03:06 <****> So left join *is* the preferred way to do no-matching-rows checks.
03:07 * Simetrical wonders why he uses *asterisks* for emphasis when IRC does, after all, support bold
03:07 <****> my client bolds any word enclosed in * anyway
03:07 <****> Simetrical, okay, *testing the query*
03:08 <****> yes the join would be preferred to the subquery
03:08 <****> Right.
03:08 * thoughtful thinks possibly because some clients don't support formatting?
03:08 <****> Pidgin doesn't appear to, for one.
03:08 <****> Nah, screw other clients.
03:09 <****> i have 2 identical databases and i'm trying to overwrite some column from db1 in db2 for certain rows. how can i do that? i would like to use ID or some other unique field to tell mysql what should be changed where.
03:09 <****> Simetrical, yeah it worked perfectly! thx a lot. btw, whats the difference between the various join-types? i.e. inner-, outer, -left, -right?
03:10 <****> mEck0: right is useless
03:10 <****> s/Simetrical/ToeBee
03:10 <****> :p
03:10 <****> chadmaynard, I have mostly used inner join, don't know the difference between the others
03:10 <****> mEck0: right is the same thing as left but written with table a and table backwards. Don't use right join!
03:10 <****> mEck0, LEFT JOIN includes at least one row from the table on the left even if there's no matching row on the right. INNER JOIN (or just JOIN) only includes rows if there's a match. Ignore the others.
03:11 <****> UPDATE db1.users set pass in (select pass from db2.users) where ... ?!?
03:11 <****> chadmaynard, can you help with this: http://dpaste.com/22003/
03:11 <****> Simetrical, aha, i got it, thx for the info
03:11 <****> robboplus, makes no sense. You have to specify a value to set pass to, "in ..." doesn't fit there.
03:12 <****> Simetrical i know.. prob is that i don't know what does make sense, trying to do something really important and don't want to screw it up
03:12 <****> fg3: help you do what?
03:12 <****> !m mEck0 join
03:12 <****> oh... the wench isn't back yet :(
03:12 <****> robboplus, what is it you want to set the value to?
03:12 <****> chadmaynard, solve the mystery at the top
03:12 <****> Simetrical ok i will try to explain
03:13 <****> ToeBee, what do you mean ?
03:13 <****> chadmaynard: go fix the wench!
03:13 <****> fg3: can I have a mysqldump?
03:13 <****> ToeBee: why me?
03:13 <****> chadmaynard, unfortunately I cannot
03:13 <****> mEck0: usually there is a bot in here that will give URLs. I was attempting to trigger it
03:13 <****> fg3: they look like horrendous queries
03:13 <****> Simetrical i'm trying to update column "pass" (3000 rows) in db2 with data from column "pass" in db1. but i need to take them one by one and make sure that pass will match particular user, e.g. they have their unique logins that i could use. NO idea how to build such query
03:14 <****> mEck0: http://dev.mysql.com/doc/refman/5.0/en/join.html
03:14 <****> for more info on joins
03:14 <****> chadmaynard, yes
03:14 <****> ToeBee, thx
03:14 <****> fg3: i'm guessing you have an order of operations error. your outmost boolean expression should be an OR
03:14 <****> robboplus, what is the exact requirement it needs to satisfy to "match particular user"? You have to be very specific and precise when using any kind of computer language.
03:14 <****> Simetrical and i'm really depressed... i just made a big mistake and trying to restore some data from the backup.
03:15 <****> chadmaynard, that's a start
03:15 <****> Is there anything bad about LOCATE()?
03:15 <****> fg3: It appears to be an and at a glance
03:15 <****> Simetrical ok trying again... let's say i have a user that goes by login "peter" in db1. i need to copy the pass from column "pass" in db1 to column "pass" in db2 where login is "peter"
03:16 <****> blueredfrog: it doesn't use indexes just like any function
03:16 <****> Simetrical same for all the rest... 3000 rows
03:16 <****> chadmaynard, thanks for pointing me in the right direction
03:16 <****> robboplus: update join
03:16 <****> chadmaynard: Well, is there an alternative?
03:16 <****> Simetrical so it's like: get the row, take the username, take his pass (all db1) AND find the user, put his pass there (db2).
03:17 <****> Is LOCATE() even in the SQL standard?
03:17 <****> chadmaynard i have never used JOIN in my life yet..
03:17 <****> blueredfrog: of course not
03:18 <****> robboplus, I think this will work. Obviously, back up first. UPDATE db2.login JOIN db1.login ON db1.login.username=db2.login.username SET db1.login.password=db2.login.password;
03:18 <****> robboplus: update db1.table1 t1, db2.table2 t2 set t1.pass = t2.pass where t1.id = t2.id
03:18 <****> chadmaynard: Then it is impossible to do this in "real" SQL? "Find out if there is any rows in table X (for column Y) which contains a given string literal."?
03:18 <****> chadmaynard, how do the results get duplicate values for bug_id (if I leave GROUP BY Bug_id off ) ?
03:18 <****> Simetrical, chadmaynard: thank you very much - i will try it and of course i have my backups now. i'm just so nervous because of what has happened that i can't think at all, let me try that stuff now
03:18 <****> blueredfrog: sure with like
03:19 <****> fg3: heh?
03:19 <****> chadmaynard: Are you sure? That is sort of "backwards".
03:19 <****> blueredfrog, why is it backwards to use LIKE?
03:19 <****> chadmaynard, I'll get back to you on that - gotta get some food
03:20 <****> blueredfrog: backwards? switch the columns
03:20 <****> blueredfrog: no, that would be the SQL solution to your situation.
03:20 <****> Simetrical: Because if you read the question carefully, I want to check the table rows from user input... not the other way around.
03:21 <****> I have a list of banned domains in table "banned_domains". It has but one column ("domain") which is a VARCHAR(64). I have a bunch of rows (about 200+ currently). Now, I wanna check if a given string is contained in any of those rows.
03:21 <****> blueredfrog: I hear this exact question ALL the time. I think i'll make a hashmysql entry.
03:21 <****> Simetrical, that query wont work it doesnt make any changes, and there are definitely records which should be changed
03:21 <****> For this I had to use LOCATE()...
03:21 <****> SELECT domain FROM banned_domains WHERE domain LIKE '%yourcheckvalue%'
03:21 <****> blueredfrog, SELECT * FROM banned_domains WHERE domain LIKE '%mystring%';
03:22 <****> Wait... hmm... I thought a lot about this, and what you say was my first thought, yes.
03:22 <****> But there was some problem with it.
03:22 <****> it doesn't use an index?
03:22 <****> Nah... something that made it impossible by nature.
03:23 <****> that would be very zen.
03:23 <****> could someone please tell me whats wrong with this? http://pastebin.com/m349cb118
03:23 <****> Hey, I've got a table set up like, id|parentId|name, where parentId links to id. I'd like to get every parent (or at times every child) of an id. Can I do that?
Total 31 pages. You are browsing page 5/31.
First :: Prev :: [...] [3] [4] [5] [6] [7] [...] :: Next :: Last