UPDATE membership SET account = CASE WHEN(JSON_EXTRACT(payload, "$.pack.account") IS NOT NULL) THEN JSON_EXTRACT(payload, "$.pack.account") ELSE account END
Importing large dumps
The first step is remove every CREATE DATABASE and USE db_name instruction. If the dump is very large the best way to achieve this is using sed since opening a large dump in a text editor is a very bad idea.
sed -i '/CREATE DATABASE.*/c\' dump.sql sed -i '/USE whatever.*/c\' dump.sql
Sometimes when you are importing large dumps you get errors like Can't connect to the server and ERROR 2047 (HY000): Wrong or unknown protocol. To try to mitigate this issue you can set some mysql parameters:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17
mysql -u root -p
set global net_buffer_length=1000000; --Set network buffer length to a large byte number
set global max_allowed_packet=1000000000; --Set maximum allowed packet size to a large byte number
SET foreign_key_checks = 0; --Disable foreign key checking to avoid delays,errors and unwanted behaviour
-- Create new schema to import the dump CREATE DATABASE my_schema;
-- Select the new created database USE my_schema;
source file.sql --Import your sql dump file
SET foreign_key_checks = 1; --Remember to enable foreign key checks when procedure is complete!