mysql - Write delete query using GROUP BY, HAVING -

-

i have mysql query group by, having.

query working fine need delete same records, , when trying delete these record getting error.

select query

select * user_location_history (timestampdiff(day,from_unixtime(location_date/1000),sysdate())>30) , user_id in (select user_id user_location_history (timestampdiff(day,from_unixtime(location_date/1000),sysdate()) < 30) group user_id having count(1) > 100) order id asc limit 200

delete query

delete user_location_history (timestampdiff(day,from_unixtime(location_date/1000),sysdate())>30) , user_id in (select user_id user_location_history (timestampdiff(day,from_unixtime(location_date/1000),sysdate()) < 30) group user_id having count(1) > 100) order id asc limit 200

error

error code: 1093 can't specify target table 'user_location_history' update in clause

one way around problem wrap subquery in subquery:

delete user_location_history  timestampdiff(day,from_unixtime(location_date/1000),sysdate())>30        ,         user_id in (           select user_id            (select user_id                  user_location_history                  timestampdiff(day,                                     from_unixtime(location_date/1000),                                     sysdate()) < 30                 group user_id                  having count(1) > 100) t) order id asc limit 200

Comments

Post a Comment

Popular posts from this blog

How to check data type in C++? -

-
i'm new c++, i've been using python. i'm trying check type of variable of value stored in objects i'm working on. remember in python there comand isinstance use condition run commands, if next value string, a, , if it's int b. is there way check what's data type on variable in c++? example: in python had array math operation, each character in field [3,"+",2] as read array separate ints strings isinstance command if isinstance(list[0],int): aux1.append(list[0]) list=list[1:] else: if isinstance(lista[0],str): aux2.append(list[0 list=list[1:] now in c++ need similar, time each character in node linked list , again, need separate them, ints in linked list, , strings in linked list what seem struggling c++ statically , (relatively) typed language. discussion each of these terms mean refer this other question , it's explained there better could. first of should sure need...
Read more

javascript - Is getTimezoneOffset() stable during daylight saving transition? -

-
i have date time below conversion logic in javascript converting utc time passed timezone's local time. wondering logic work fine during daylight transition? if not remedy that? can't use third party library. have use pure javascript or angular js. function mytime() { var d1= document.getelementbyid("txtdate").value; var zoffset = document.getelementbyid("txtoffset").value; console.log("date1",d1); var d2 = new date(d1.replace(/ /g,'t')); var d3= d2.gettime()+(d2.gettimezoneoffset()*60000); console.log("date2",d2); console.log("date3",d3); var d4 = new date(d3 + (3600000 * zoffset)); console.log("date3",d3); console.log("date 4",d4); var d5 = d4.tolocaletimestring(); console.log("date 5",d5); ...
Read more

sql server - SQL Query returns one result, does not return 0 or NULL result -

-
i trying query list advisers , provide count of active students each. can list advisers have 1 student, exclude more 1, can not return advisers 0 or null count. select advisors.advisorid, advisors.firstname, advisors.lastname, count(case students.isactive when '1' 1 else null end) "number of students" advisors, students advisors.advisorid=students.advisorid group advisors.advisorid, advisors.firstname, advisors.lastname having count(case students.isactive when '1' 1 else null end)='1' counts active studnets, , returns advisor list advisor 1 student, advisers 0 students come blank. missing? select advisors.advisorid, advisors.firstname, advisors.lastname, count(case students.isactive when '1' 1 else null end) "number of students" advisors, students advisors.advisorid=students.advisorid group advisors.advisorid, advisors.firstname, advisors.lastname having count(case students.isactive when '1' 1 else null end) null ...
Read more