List:General Discussion« Previous MessageNext Message »
From:Tanner Postert Date:April 18 2007 7:42am
Subject:Re: replace / insert into with sum not working
View as plain text  
i've tried a flat insert without the () around the select, i just put them
there for easier readability to a coworker. i get the same result from the
query,

insert into summary

select sum(x) as x, .....
from table
where condition = 'true'
group by whatever


error: FUNCTION: database_name.sum is not a function. is this a bug?


On 4/17/07, Michael Dykman <mdykman@stripped> wrote:
>
> In most common places where a VALUES list is specified you can
> substitute a select statement which produces such a list. SQL is an
> algebra after all.  The only thing wrong with the syntax of the
> original poster was the braces around the select statement itself.
> Drop them and it should work fine.
>
> Now the REPLACE might be more elegantly handled with a INSERT...ON
> DUPLICATE KEY UPDATE.. construct, but that wasn't the question...
>
> On 4/17/07, Baron Schwartz <baron@stripped> wrote:
> > Hi Tanner,
> >
> > Tanner Postert wrote:
> > > i'm using the following query:
> > >
> > > REPLACE INTO vviews_total(
> > > SELECT uuser_id, sum( vviews.views ) AS views, sum( vviews.embeds ) AS
> > > embeds, sum( vviews.plinks ) AS plinks, sum( vviews.`30d` ) AS 30d,
> sum(
> > > vviews.`7d` ) AS 7d, sum( vviews.`24h` ) AS 24h, sum( vviews.site30d )
> AS
> > > site30d, sum( site7d ) AS site7d, sum( vviews.site24h ) AS site24h,
> sum(
> > > vviews.click ) AS click, now( ) AS last_dt
> > > FROM vviews
> > > JOIN video ON ( video.id = vviews.video_id )
> > > WHERE video.nsfw =0
> > > GROUP BY vviews.uuser_id )
> > >
> > >
> > > the query works fine. it grabs the SUM for each of the fields and sums
> > them
> > > to insert into the other table, but when i try to run the above
> query.. .i
> > > get an errors
> > >
> > > FUNCTION db.sum does not exist.
> >
> >
> > I think the syntax you want is
> >
> > REPLACE INTO tbl (col, col, ... col) SELECT...
> >
> > Not
> >
> > REPLACE INTO tbl(SELECT)
> >
> > Baron
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
> >
> >
>
>
> --
> - michael dykman
> - mdykman@stripped
>
> - All models are wrong.  Some models are useful.
>

Thread
replace / insert into with sum not workingTanner Postert17 Apr
  • Re: replace / insert into with sum not workingBaron Schwartz18 Apr
    • Re: replace / insert into with sum not workingMichael Dykman18 Apr
      • Re: replace / insert into with sum not workingTanner Postert18 Apr
Re: replace / insert into with sum not workingTanner Postert18 Apr