These are MySQL Examples
Note these are MySQL syntax examples.
These examples are NOT a recommendation for use with Parts.
Use at your Own Risk !
Related Link:
Safe mode Update Methods
'Enable (force) use of Where Clause or Use LIMIT
SET SQL_SAFE_UPDATES = 1;
'Disable need for Where Clause or Use LIMIT
SET SQL_SAFE_UPDATES = 0;
Same schema same table (redundant data)
Update (copy) fields in the same table with LIMIT
START transaction;
UPDATE parts SET `Symbol Path` = `Library Path` LIMIT 10000;Commit;
START transaction;
UPDATE parts SET `Symbol Ref` = `Library Ref` LIMIT 10000;
Commit;
START transaction;
UPDATE parts SET `Symbol Ref` = `Library Ref` LIMIT 10000;
Commit;
START transaction;
UPDATE parts SET `Footprints Path` = `Footprint Path` LIMIT 10000;Commit;
One Schema (parts) with two tables (parts and work)
Clone a MySQL Table (Copy Structure and Insert Data)
CREATE TABLE parts.work LIKE parts.parts;
or
CREATE TABLE work LIKE parts;
Then
INSERT INTO work SELECT * FROM parts;
Delete ALL records schema.table Caution !
TRUNCATE parts.parts;
TRUNCATE parts.work;
Insert Primary Keys into parts table from work table
INSERT IGNORE INTO parts (id)
SELECT id
FROM work;
Update fields in parts table from work table
START transaction;
UPDATE parts
JOIN work ON parts.id = work.id
SET parts.`Part_Type` = work.`Part_Type`;
Commit;
START transaction;
UPDATE parts
JOIN work ON parts.id = work.id
SET parts.`Symbol Ref` = work.`Library Ref`;
Commit;
etc . . .
START transaction;
UPDATE parts
JOIN work ON parts.id = work.id
SET parts.sorted_value = work.sorted_value;
Commit;
Two Schemas with same table names work.parts parts.parts
Create work database with schema similar to parts
Connection string similar to parts, except DATABASE=parts is DATABASE=work
DATABASE=work;
-- MySQL 8.0 Script to Create work Schema and parts Table
CREATE SCHEMA `work` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE TABLE `work`.`parts`;
-- Delete all records in work.parts (schema.table)
TRUNCATE work.parts;
INSERT IGNORE INTO parts.parts (id)
SELECT id
FROM work.parts;
START transaction;
UPDATE parts.parts
JOIN work.parts ON parts.parts.id = work.parts.id
SET parts.parts.`Part_Type` = work.parts.`Part_Type`;
Commit;
etc . . .
Delete (Drop) Table Caution !
DROP TABLE parts.work;
Example: Server Side Table to Table Update
DROP TABLE IF EXISTS `TESTLOAD`;
CREATE TABLE `TESTLOAD` SELECT NAME,VALUE FROM TEST WHERE 1=2;
LOAD DATA LOCAL INFILE 'C:/testData.txt'
INTO TABLE `TESTLOAD`
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES
(NAME, VALUE);
INSERT INTO `TEST` (NAME, VALUE)
SELECT NAME, VALUE FROM `TESTLOAD`
ON DUPLICATE KEY UPDATE VALUE = VALUES(VALUE);
DROP TABLE `TESTLOAD`;Source Stack OverFlow
