EC
1746
MySQLERRORNotableDDLHIGH confidence

Cannot update table in CREATE TABLE ... SELECT

Production Risk

Low — statement rejected; no data changed.

What this means

A CREATE TABLE ... SELECT statement attempted to update a table that is already being selected from, which is not permitted.

Why it happens
  1. 1The SELECT part of CREATE TABLE ... SELECT references the same table being created or a table involved in the DML portion.
How to reproduce
trigger — this will error
trigger — this will error
CREATE TABLE new_tbl SELECT * FROM src WHERE EXISTS (UPDATE src SET col=1);

expected output

ERROR 1746 (HY000): Can't update table 'src' in create table.

Fix

Separate the UPDATE and CREATE TABLE operations

Separate the UPDATE and CREATE TABLE operations
UPDATE src SET col = 1;
CREATE TABLE new_tbl SELECT * FROM src;

Why this works

Performs the update independently before creating the new table from the result.

What not to do

✕

Sources
Official documentation ↗

MySQL 8.0 — 1746 ER_CANT_UPDATE_TABLE_IN_CREATE_TABLE_SELECT

Content generated with AI assistance and reviewed for accuracy. Found an error? hello@errcodes.dev

← All MySQL errors