clear markdown compare help donate or buy diddy dollings comments/suggestions/bugs a leap of faith? bible365
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 818545 fiddles created (9195 in the last week).

CREATE TABLE currency1 (id INT AUTO_INCREMENT, PRIMARY KEY (id), val VARCHAR(3)) SELECT 1 id, 'USD' val UNION ALL SELECT 2, 'EUR'
 hidden batch(es)


CREATE TABLE payment1 (id INT AUTO_INCREMENT, PRIMARY KEY (id), currency_id INT, FOREIGN KEY (currency_id) REFERENCES currency1 (id), amount INT) SELECT 1 id, 1 currency_id, 11 amount UNION ALL SELECT 2 id, 2 currency_id, 22 amount
 hidden batch(es)


SELECT val currency_name, amount FROM payment1 JOIN currency1 ON payment1.currency_id = currency1.id
currency_name amount
USD 11
EUR 22
 hidden batch(es)


CREATE TABLE currency2 (id INT AUTO_INCREMENT, PRIMARY KEY (id), val VARCHAR(3)) SELECT 1 id, 'USD' val UNION ALL SELECT 2, 'GBP'
 hidden batch(es)


CREATE TABLE payment2 (id INT AUTO_INCREMENT, PRIMARY KEY (id), currency_id INT, FOREIGN KEY (currency_id) REFERENCES currency2 (id), amount INT) SELECT 1 id, 1 currency_id, 111 amount UNION ALL SELECT 2 id, 2 currency_id, 222 amount
 hidden batch(es)


SELECT val currency_name, amount FROM payment2 JOIN currency2 ON payment2.currency_id = currency2.id
currency_name amount
USD 111
GBP 222
 hidden batch(es)


-- Prepare currency1 table for inserting data ALTER TABLE currency1 ADD UNIQUE INDEX currency_name (val)
 hidden batch(es)


-- Insert currencies INSERT IGNORE INTO currency1 (val) SELECT val FROM currency2
 hidden batch(es)


SELECT * FROM currency1
id val
2 EUR
3 GBP
1 USD
 hidden batch(es)


-- Prepare payment1 table for inserting data ALTER TABLE payment1 ADD COLUMN source INT DEFAULT 1
 hidden batch(es)


-- Insert payments INSERT INTO payment1 (currency_id, amount, source) SELECT currency1.id, payment2.amount, 2 FROM payment2 JOIN currency2 ON payment2.currency_id = currency2.id JOIN currency1 ON currency2.val = currency1.val
 hidden batch(es)


-- Check the result SELECT val currency_name, amount, source FROM payment1 JOIN currency1 ON payment1.currency_id = currency1.id
currency_name amount source
USD 11 1
EUR 22 1
USD 111 2
GBP 222 2
 hidden batch(es)