Alternative - Error Code: 1093. You can't specify target table 'YOUR_TABLE' for update in FROM clause

 

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

UPDATE YOUR_TABLE SET columnA = 'def' WHERE id IN (SELECT id AS MMID FROM YOUR_TABLE WHERE columnA = 'abc');

UPDATE YOUR_TABLE SET columnA = 'def' WHERE id IN ( SELECT MMID FROM ( SELECT id AS MMID FROM YOUR_TABLE WHERE columnA = 'abc' ) AS YOUR_ALIAS );

Export connections in MySQL Workbench

saw in log:

%USERPROFILE%\AppData\Roaming\MySQL\Workbench\connections.xml


cd %USERPROFILE%\AppData\Roaming\MySQL\
zip up whole folder: Workbench

restore in new laptop, and done.

you can get what you have in previous laptop, the price to pay so far is: rekey in all passwords for all connections.

Unable update table where clause in same sub table.

 




https://stackoverflow.com/a/43610081

If you can't do

UPDATE table SET a=value WHERE x IN
    (SELECT x FROM table WHERE condition);

because it is the same table, you can trick and do :

UPDATE table SET a=value WHERE x IN
    (SELECT * FROM (SELECT x FROM table WHERE condition) as t)

[update or delete or whatever]

Is working - while create function: ERROR 1418 (HY000) & ERROR 1267 (HY000)

 

SHOW VARIABLES LIKE '%char%';

ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)

Source URL: https://stackoverflow.com/a/26015334

1. Execute the following in the MySQL console:

   SET GLOBAL log_bin_trust_function_creators = 1;

ERROR 1267 (HY000): Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '='

drop table

and recreate table with 

CREATE DATABASE new_table DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_unicode_ci;

MySql Select statement export to a file.

SELECT column_a, column_b, column_c FROM tb_abc INTO OUTFILE 'C:/folders/location/tb_abc.txt' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n';