0

Respected all, I have the following query that is executed only for a date by the filter that is noted, what I need is to run for all the dates in the table, and I can not find the way to indicate that function, I appreciate its special orientation:

update scraper_data_twitter as T1,
    (
        select Ntweets as Ntweets_var,
            (
                select COUNT(Ntweets) + 1
                from scraper_data_twitter
                where (NTweets > Ntweets_var)
                    and date = '2017-02-13'
                ) as rank
        from scraper_data_twitter
        where date = '2017-02-13'
        group by SITE,
            date
        order by NTweets_var desc
        ) as A
set T1.rnk_Ntweets = A.rank
where T1.ntweets = A.Ntweets_var
Gurwinder Singh
  • 38,557
  • 6
  • 51
  • 76
LeoCP
  • 3
  • 2
  • Can you not just remove the 2 `WHERE Date = '2017-02-13'` and then it runs on all rows regardless of date – RiggsFolly Mar 01 '17 at 17:44
  • You are using mysql? Normally it complains if you UPDATE and SELECT the same table in one SQL statement. Did you tag your question correctly? – Bill Karwin Mar 01 '17 at 17:45
  • @BillKarwin It does? Are you sure? – Strawberry Mar 01 '17 at 17:49
  • @Strawberry In this case, as I test this query it appears to be okay, perhaps because the SELECT is double-nested in subqueries. But it's usually error `#1093 - You can't specify target table 'xxxx' for update in FROM clause`, for example http://stackoverflow.com/questions/45494/mysql-error-1093-cant-specify-target-table-for-update-in-from-clause – Bill Karwin Mar 01 '17 at 18:07
  • RiggsFolly: I have removed the WHERE clause, and it generates a single ranking for the whole table, and I need a break by date. – LeoCP Mar 02 '17 at 02:43
  • Bill Karwin: Sure, I'm using MYSQL, and this query works OK, my need is that I need it to be done for all dates, since to illustrate it, put the query filtered by date '2017-02-13', and it does fine, What I have not found the method, the condition, to do this for all dates (I'm not an expert and that's why I'm asking for help) – LeoCP Mar 02 '17 at 02:43
  • @Strawberry I have been browsing for your suggestions but I still do not detect the right option for my query! – LeoCP Mar 02 '17 at 02:43

0 Answers0