Mysql – Problem with nested UPDATE queries

MySQLupdate

I cannot make an update query work. As a first step, the following one works correctly:

UPDATE Tab1  
SET Tab1.a = '3'
WHERE
    Tab1.id IN ( 123, 456 );

where id is the primary key of Tab1.
However when I add a select to get the set of values Tab1.id must be in, I get the following MySQL error:

Error code: 1093
You can't specify target table 'Tab1' for update in FROM clause.

This is the complete query:

UPDATE Tab1  
    SET Tab1.a = '3'
    WHERE
        Tab1.id IN ( 
            SELECT Tab1.id 
            FROM Tab1, Tab2 
            WHERE Tab1.b = Tab2.b AND Tab1.c = '4'
        );

I cannot see how to fix this error.
Am I forced to split this query in two?

Thank you!

Best Answer

Try

UPDATE Tab1 JOIN Tab2 ON Tab1.b = Tab2.b
SET Tab1.a = '3'
WHERE Tab1.c = '4'
;

Check the documentation for further information (look for 'multiple-table syntax'). An important point is:

If you use a multiple-table UPDATE statement involving InnoDB tables for which there are foreign key constraints, the MySQL optimizer might process tables in an order that differs from that of their parent/child relationship. In this case, the statement fails and rolls back. Instead, update a single table and rely on the ON UPDATE capabilities that InnoDB provides to cause the other tables to be modified accordingly. See Section 14.3.5.4, “FOREIGN KEY Constraints”.

You may check a similar example on SQLFiddle.

SUGGESTION #1

Why use a subquery at all? Try it like this:

update Programs 
set category="Adobe Suites" 
where programName regexp '^adobe.*cs[1-9]$';

PROS AND CONS : Simpler query with no subquery, but a single-pass full table scan.

SUGGESTION #2

Put the distinct names in a separate table and then do an UPDATE JOIN:

create table ProgramNames
    select distinct programName 
    from Programs
    where programName regexp '^adobe.*cs[1-9]$'
;
alter table ProgramNames add primary key (programName);
update Programs A INNER JOIN ProgramNames B USING (programName)
set A.category="Adobe Suites";
drop table ProgramNames;

PROS AND CONS : Clean UPDATE JOIN, but requires creating the temp table manually. This will be great if programName is indexed in the Programs table.

CAVEAT

There is great reason why you get that error message. It has to do with the MySQL Query Optimizer. Back on Feb 22, 2011, I wrote an answer to this post : Problem with MySQL subquery. I mentioned that there are times the MySQL Query Optimizer can make rows disappear intermittently in the optimization process. The join would not be clean internally. So, rather than rewrite it, MySQL simply kicks out an error so as not to have to rewrite. IMHO I think that maybe due to the way MySQL interacts with storage engines.

Give it a Try !!!

MySQL 5.6 DateTime Incorrect datetime value: ‘2013-08-25T17:00:00+00:00’ with Error Code 1292

'2013-08-25T17:00:00+00:00'

This is a valid iso-8601 datetime value, but it is not a valid MySQL datetime literal. On that point, the developer is incorrect.

The documentation explains what ALLOW_INVALID_DATES does:

Check only that the month is in the range from 1 to 12 and the day is in the range from 1 to 31.

In other words, 2013-02-31 would be a permissible date if allow_invalid_dates is set. This option does not do anything when the date or datetime isn't even in a valid format for MySQL.

The +00:00 is the timezone offset from UTC. In this case, the time expressed is in UTC, so the offset is zero hours, zero minutes.

Your workaround would be to remove the STRICT_TRANS_TABLES from the sql_mode that is a default in the config file created during the MySQL 5.6 installation process... you need to carefully consider the implications of changing this, but it does allow the data to go in.

mysql> select @@sql_mode;
+--------------------------------------------+
| @@sql_mode                                 |
+--------------------------------------------+
| STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
+--------------------------------------------+
1 row in set (0.00 sec)

mysql> insert into datetimetest(dt) values ('2013-08-26T12:00:00+00:00');
ERROR 1292 (22007): Incorrect datetime value: '2013-08-26T12:00:00+00:00' for column 'dt' at row 1

-- remove STRICT_TRANS_TABLES -- note that executing this only removes it for your
-- current session -- it does not make a server-wide config change

mysql> set @@sql_mode='no_engine_substitution';
Query OK, 0 rows affected (0.00 sec)

mysql> select @@sql_mode;
+------------------------+
| @@sql_mode             |
+------------------------+
| NO_ENGINE_SUBSTITUTION |
+------------------------+
1 row in set (0.00 sec)

-- now MySQL will accept the invalid value, with a warning

mysql> insert into datetimetest(dt) values ('2013-08-26T12:00:00+00:00');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+-----------------------------------------+
| Level   | Code | Message                                 |
+---------+------+-----------------------------------------+
| Warning | 1265 | Data truncated for column 'dt' at row 1 |
+---------+------+-----------------------------------------+
1 row in set (0.00 sec)

-- the value did get inserted, but the time zone information was lost:

mysql> select * from datetimetest;
+----+---------------------+
| id | dt                  |
+----+---------------------+
|  1 | 2013-08-26 12:00:00 |
+----+---------------------+
1 row in set (0.00 sec)

Best Answer

Related Solutions

Mysql – Sub-query in update function

SUGGESTION #1

SUGGESTION #2

CAVEAT

Give it a Try !!!

MySQL 5.6 DateTime Incorrect datetime value: ‘2013-08-25T17:00:00+00:00’ with Error Code 1292

Related Question