#mysql
04 November 2007
Total 16 pages. You are browsing page 4/16.
First :: Prev :: [...] [2] [3] [4] [5] [6] [...] :: Next :: Last
05:19 <****> JOIN SongRatings SR ON SR.songid = S.songid
05:19 <****> SET S.avgrating = AVG(SR.rating)
05:19 <****> WHERE S.songid = 1
05:19 <****> oh yea, I invite all members of #mysql to join #pgd
05:19 <****> oh heh. If there isn't any activity, I don't even notice I'm in there :)
05:20 <****> ToeBee: haha they never talk
05:20 <****> DONT DO IT!!! AVOID PGD!!!!!
05:20 <****> needs to actually be a left join, but I just rewrote it from memory
05:20 <****> ToeBee: they got PISSED when i set it invite only though.
05:20 <****> heh
05:20 <****> blobaugh: why did you leave?
05:21 <****> you want me to cause more havok in another room?
05:21 <****> yea
05:21 <****> oh, ok
05:23 <****> chadmaynard, i'm not getting much join with (DELETE t1.* FROM table t1 JOIN table t2) -- ... right syntax to use near 't1.* FROM ...'
05:23 <****> i also chased down some variants of that (not giving the table an alias, ...)
05:23 <****> ericP: select version();
05:24 <****> chadmaynard, 5.0.45-Debian_1-log -- probably weeks old, i guess
05:24 <****> hmmm
05:24 <****> ericP: that should be correct.
05:25 <****> chadmaynard, i'll update pastebin to give you my exact DELETE...
05:25 <****> ok
05:25 <****> ericP: blobaugh will examine it
05:27 <****> chadmaynard, (blobaugh), http://pastebin.com/d1c578129
05:28 <****> chadmaynard, last DELETE trigger should correspond to the last INSERT trigger
05:29 <****> chadmaynard, updating with the substituted example i was testing with...
05:30 <****> ericP: i don't see a syntax error in the last statement alone. What's the error?
05:32 <****> chadmaynard, bottom of http://pastebin.com/d5153614f shows the adverse feedback i am getting
05:34 * blobaugh kicks ericP's ass
05:35 <****> bring it
05:35 <****> ericP: try the USING version
05:35 <****> ericP: or just DELETE match FROM
05:37 <****> "DELETE match FROM trans_hierarchy AS match" gave me an error at "match", which means it wasn't startled into hiding when it saw "AS"
05:37 * blobaugh hands ericP a saber
05:37 <****> uh, is "match" a keyword?
05:37 <****> im using a fork. you ready?
05:37 <****> ericP: yes
05:37 <****> oh, dude. a dual
05:37 <****> ericP: glad you caught it. I wasn't thinking
05:38 <****> chadmaynard, rock! s/match/fidip/g fixed it
05:38 <****> thats the only think i like about phpmyadmin. it hightlights keywords if i use them by accident
05:38 <****> ericP: what about me?
05:38 <****> blobaugh, please be gentle -- it's my first time getting forked
05:38 <****> porked?
05:39 <****> is that when i scream like a pig?
05:39 <****> lol
05:39 <****> looks like you can do multi-table update with a group by, seems like it-- might need to use a sub query, just fyi in case anyone was curious, or can tell me that i'm wrong
05:40 <****> group by on an update?
05:40 <****> kpdvx_, i'd be curious to see the update when i get it worked out
05:40 <****> dang i wish i could figure out how to do a page up in an osx terminal
05:40 <****> multi-table with a join
05:40 <****> with an aggregate fn (AVG)
05:40 <****> why are you having to group it though?
05:41 <****> you guys make it hard to get the dishes washed
05:41 <****> i dont believe that i do need to- the error i receive with the query is ERROR 1111 (HY000): Invalid use of group function
05:42 <****> pastebin your query
05:42 <****> kpdvx_, ala this? INSERT INTO averages (name, averageScore) VALUES ("bob", (SELECT AVG(score) FROM scores WHERE name="bob" GROUP BY (score)
05:42 <****> you just trying to update multiple tables?
05:42 <****> i can paste the query
05:43 <****> kpdvx_, best pastebin -- irc-ers are touchy about pastes
05:43 <****> ericP, the subquery should work for me, though i havent tried it yet- just trying to avoid a subquery
05:43 <****> sure thing
05:43 <****> i think we should change the rule to no pastebins one day and ban everyone that uses a bin. That'd be entertaining.
05:44 <****> chadmaynard, the next day we can ban everyone who makes rules (including this one)
05:44 <****> ericP: you are going to ban me?
05:44 <****> heh
05:44 * chadmaynard hides
05:44 <****> ericP: its easier to read, and it doesnt scroll away from you
05:45 <****> chadmaynard: i like that idea
05:45 <****> chadmaynard: i still think we should have a bot kick anyone who hasnt talked in a week
05:45 <****> blobaugh: haha
05:45 <****> if they keep autojoining a one day ban
05:45 <****> http://pastebin.com/d56f7b751
05:46 <****> blobaugh: our user count would drop big time
05:46 <****> all the join/quit notices irritate me
05:46 <****> user count? who cares? they dont talk anywho
05:46 <****> i bet 15% of our users are bots
05:46 <****> kpdvx_: you probably want to GROUP BY SR.songid
05:47 <****> or S.songid but that'd be confusing
05:47 <****> chadmaynard, i try that and i get ERROR 1064 (42000): You have an error in your SQL syntax, etc
05:47 <****> kpdvx_: show me how you'd type it
05:48 <****> oh you mean in the update?
05:48 <****> http://pastebin.com/d221d52e8
05:48 <****> yes, in the update
05:48 <****> you can't group in update
05:49 <****> use a subquery
05:49 <****> ya, that's what i figured
05:49 <****> just wanted to see if there was a way around that
05:49 <****> lol, the page just loaded and i was gonna say subquery
05:49 <****> simple and super easy to see
05:49 <****> kpdvx_: why?
05:49 <****> my dislike of subquerys is probably misplaced, but usually i see them as detrimental to performance
05:49 * blobaugh rummages for desert
05:50 <****> selects are very fast
05:50 <****> kpdvx_: you are a performance nazi
05:50 <****> especially simple selects like you'd use there
05:50 <****> kpdvx_, why don't you want a subquery?
05:50 <****> maybe faster than how you have it now
05:50 <****> ericP: he just told us....
05:50 <****> besides whats a .00004 difference?
Total 16 pages. You are browsing page 4/16.
First :: Prev :: [...] [2] [3] [4] [5] [6] [...] :: Next :: Last