Search code examples
mysqlsqlinsert-into

Data migration script INSERT INTO

I've created following SQL code to migrate our database. The UP query is working fine, but I just can't figure out how to fix the DOWN query. I just want to insert all data that is in the payment_gateway table but must also match the correct reseller_shop.id.

// UP
INSERT INTO payment_gateway (`type`, account, keyIn, keyOut, resellerShopId)
SELECT 'paymentGatewayOgone', rs.ogoneAccount, rs.ogoneSignatureIn, rs.ogoneSignatureOut, rs.id
FROM reseller_shop AS rs
WHERE rs.ogoneAccount <> '';

// DOWN
INSERT INTO reseller_shop (ogoneAccount, ogoneSignatureIn, ogoneSignatureOut)
SELECT pg.account, pg.keyIn, pg.keyOut
FROM payment_gateway AS pg
WHERE pg.type = 'paymentGatewayOgone'
AND reseller_shop.id = pg.resellerShopId
Solution

  • Seemed that I had to use UPDATE instead of INSERT INTO.

    UPDATE reseller_shop AS rs, payment_gateway AS pg
SET rs.ogoneAccount = pg.account, rs.ogoneSignatureIn = pg.keyIn, rs.ogoneSignatureOut = pg.keyOut
WHERE pg.type = 'paymentGatewayOgone'
AND rs.id = pg.resellerShopId