MySQL Workbench - SQL Query Examples

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:

Learn MySQL at POPSQL

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 `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