Error 1093 Hy000 Mysql
Contents |
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn more about hiring developers or posting error hy000 mysql odbc 5.1 driver access denied for user ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join
Error 2002 Hy000 Mysql
the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; it only takes a
Error 2002 Hy000 Mysql Centos
minute: Sign up MySQL Update Error 1093 up vote 4 down vote favorite 1 This works in a table where doc_id is the primary key: select count(*)+1 from doctor where exp > (select exp from doctor where doc_id='001'); +------------+ | count(*)+1
Error 2006 Hy000 Mysql
| +------------+ | 2 | +------------+ But when I'm using the same select query to set a field in the table, it reports the following error: update doctor set rank= ( select count(*)+1 from doctor where exp > (select exp from doctor where doc_id='001') ) where doc_id='001'; ERROR 1093 (HY000): You can't specify target table 'doctor' for update in FROM clause I can't understand which target table reference it is talking of. Can someone explain? mysql sql sql-update mysql-error-1093 share|improve this question error 1215 hy000 mysql edited May 1 '12 at 17:03 Juan Mellado 11k52642 asked Mar 15 '11 at 18:57 user191776 add a comment| 3 Answers 3 active oldest votes up vote 10 down vote accepted This restriction is documented in the MySQL manual: Currently, you cannot update a table and select from the same table in a subquery. As a workaround, you can wrap the sub-query in another sub-query and avoid that error: update doctor set rank= (select rank from ( select count(*)+1 as rank from doctor where exp > (select exp from doctor where doc_id='001') ) as sub_query) where doc_id='001'; share|improve this answer answered Mar 15 '11 at 19:46 Ike Walker 33.4k95278 3 although it makes me wonder, is that restriction supposed to protect you from something that the workaround reveals or is it a technical problem? –chustar Mar 19 '11 at 2:00 add a comment| up vote 1 down vote You can't use a table you're updating in a subquery in the from clause. Try a join, or a double subquery :) share|improve this answer answered Mar 15 '11 at 19:00 Konerak 27.2k66197 add a comment| up vote 1 down vote I think it might be because you're reading and writing from the same table. This could be a preventive way of blocking writing because your update could affect the data being read. You might need to separate the subquery into using an intermediate virtual table. share|improve this answer answered Mar 15 '11 at 19:03
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business error 2013 hy000 mysql Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation error 2003 hy000 mysql windows Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just like mysql error code 1241 you, helping each other. Join them; it only takes a minute: Sign up ERROR 1093 (HY000): You can't specify target table 'a' for update in FROM clause up vote 1 down vote favorite 1 I have this http://stackoverflow.com/questions/5316576/mysql-update-error-1093 query UPDATE trh_adminLoginDate SET superseded = true WHERE EXISTS (SELECT * FROM trh_adminLoginDate AS a2 WHERE a2.adminId = a.adminId AND a2.loginDate > a.loginDate AND a2.clientPlatform = a.clientPlatform) and table look like this. +----------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------------+--------------+------+-----+---------+----------------+ | id | bigint(20) | NO | PRI | NULL | auto_increment | | dateCreated | datetime | NO | | NULL | | | version | int(11) | http://stackoverflow.com/questions/13188002/error-1093-hy000-you-cant-specify-target-table-a-for-update-in-from-clause NO | | NULL | | | dateModified | datetime | NO | | NULL | | | adminId | bigint(20) | NO | MUL | NULL | | | clientPlatform | varchar(255) | YES | | NULL | | | loginDate | datetime | YES | | NULL | | | superseded | tinyint(1) | NO | | NULL | | +----------------+--------------+------+-----+---------+----------------+ When I execute this query I get the below error: ERROR 1093 (HY000): You can't specify target table 'a' for update in FROM clause I can create temporary table and keep the result of the sub-query and then do the UPDATE. But I don't want to do in this way. Can someone suggest me better way of doing this? mysql sql hql share|improve this question asked Nov 2 '12 at 1:26 Rakesh 98032244 add a comment| 2 Answers 2 active oldest votes up vote 3 down vote accepted You are using the alias "a" but you never define it. Perhaps this would work: UPDATE trh_adminLoginDate a JOIN trh_adminLoginDate AS a2 ON a2.adminId = a.adminId AND a2.loginDate > a.loginDate AND a2.clientPlatform = a.clientPlatform SET a.superseded = true share|improve this answer answered Nov 2 '12 at 1:58 Tom 4,47321029 thanks... it worked. –Rakesh Nov 2 '12 at 2:37 add a comment| up vote -1 down vote Or perhaps this will d
Hugo. RSS Feed. Baron Schwartz's Blog How to select from an update target in MySQL Fri, Jun 23, 2006 in Databases MySQL doesn’t allow referring to a table that’s targeted for update in a FROM clause, which can be frustrating. There’s a better way than creating endless http://www.xaprb.com/blog/2006/06/23/how-to-select-from-an-update-target-in-mysql/ temporary tables, though. This article explains how to update a table while selecting from it http://www.java2s.com/Code/SQL/Insert-Delete-Update/DeletestatementwithsubqueryERROR1093HY000YoucantspecifytargettableEmployeeSforupdateinFROMclause.htm in a subquery. The problem Suppose I want to update a table with data from a subquery that refers to the same table. I might want to do this for a variety of reasons, such as trying to populate a table with its own aggregate data (this would require assignment from a grouped subquery), updating one row from another row’s data without using hy000 mysql non-standard syntax, and so on. Here’s a contrived example: create table apples(variety char(10) primary key, price int); insert into apples values('fuji', 5), ('gala', 6); update apples set price = (select price from apples where variety = 'gala') where variety = 'fuji'; The error message is ERROR 1093 (HY000): You can't specify target table 'apples' for update in FROM clause. The MySQL manual mentions this at the bottom of the UPDATE documentation: “Currently, you cannot update a table and error 2002 hy000 select from the same table in a subquery.” It’s pretty easy to work around the problem in this contrived example, but there are times when it’s not possible to write the query without subqueries that refer to the update target. There is a workaround, though. The workaround Since MySQL materializes subqueries in the FROM clause (“derived tables”) as temporary tables, wrapping the subquery into another inner subquery in the FROM clause causes it to be executed and stored into a temporary table, then referenced implicitly in the outer subquery. The following statement will succeed: update apples set price = ( select price from ( select * from apples ) as x where variety = 'gala') where variety = 'fuji'; If you want to know more about how this works, read the relevant section in the MySQL Internals Manual. Problems this trick doesn’t avoid One common frustration this doesn’t solve is the issue of badly optimized queries in the IN() clause, which are rewritten as correlated subqueries, sometimes (usually?) causing terrible performance. Wrapping the subquery in another subquery doesn’t prevent the optimizer from rewriting it as a correlated subquery, though, unless I go to extremes. In any case it’s better to just rewrite such a query as a join. Another thing it won’t do is allow a query to refer to a temporary table more than once. Neither of these issues is
PermissionViewWhere ClauseXMLDelete statement with subquery (ERROR 1093 (HY000): You can't specify target table 'EmployeeS' for update in FROM clause) : Delete«Insert Delete Update«SQL / MySQLSQL / MySQLInsert Delete UpdateDeleteDelete statement with subquery (ERROR 1093 (HY000): You can't specify target table 'EmployeeS' for update in FROM clause) mysql> mysql> mysql> CREATE TABLE EmployeeS( -> EmployeeNO INTEGER NOT NULL, -> NAME CHAR(15) NOT NULL, -> INITIALS CHAR(3) NOT NULL, -> BIRTH_DATE DATE , -> SEX CHAR(1) NOT NULL, -> JOINED SMALLINT NOT NULL, -> STREET VARCHAR(30) NOT NULL, -> HOUSENO CHAR(4) , -> POSTCODE CHAR(6) , -> TOWN VARCHAR(30) NOT NULL, -> PHONENO CHAR(13) , -> LEAGUENO CHAR(4) , -> PRIMARY KEY (EmployeeNO) ); Query OK, 0 rows affected (0.00 sec) mysql> INSERT INTO EmployeeS VALUES (2, 'Jack', 'R', '1948-09-01', 'M', 1975, 'Stoney Road','43', '3575NH', 'Stratford', '070-237893', '2411'); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO EmployeeS VALUES (6, 'Link', 'R', '1964-06-25', 'M', 1977, 'Street1','80', '1234KK', 'Stratford', '070-476537', '8467'); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO EmployeeS VALUES (7, 'Wise', 'GWS', '1963-05-11', 'M', 1981, 'First Way','39', '9758VB', 'Stratford', '070-347689', NULL); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO EmployeeS VALUES (8, 'Mary', 'B', '1962-07-08', 'F', 1980, 'Station Road','4', '6584WO', 'Inglewood', '070-458458', '2983'); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO EmployeeS VALUES (27, 'Smith', 'DD', '1964-12-28', 'F', 1983, 'Street2','804', '8457DK', 'Eltham', '079-234857', '2513'); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO EmployeeS VALUES (28, 'Smith', 'C', '1963-06-22', 'F', 1983, 'Old Main Road','10', '1294QK', 'Midhurst', '010-659599', NULL); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO EmployeeS VALUES (39, 'Bishop', 'D', '1956-10-29', 'M', 1980, 'Eaton Square','78', '9629CD', 'Stratford', '070-393435', NULL); Query OK, 1 row affected (0.00 sec) mysql> INSERT I