CODEWITHSUNDEEP
mysqlsqlinsert-into
tom
tom

Reputation: 8359

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

Upvotes: 0

Views: 4467

Answers (3)

tom
tom

Reputation: 8359

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

Upvotes: 2

Meherzad
Meherzad

Reputation: 8553

INSERT INTO reseller_shop (ogoneAccount, ogoneSignatureIn, ogoneSignatureOut)
SELECT pg.account, pg.keyIn, pg.keyOut FROM payment_gateway AS pg 
WHERE pg.type = 'paymentGatewayOgone' AND  pg.resellerShopId in 
(select reseller_shop.id from reseller_shop_id)

Hope this helps

Upvotes: 2

Bulat
Bulat

Reputation: 6979

Some thing like this:

INSERT INTO reseller_shop (ogoneAccount, ogoneSignatureIn, ogoneSignatureOut)
SELECT pg.account, pg.keyIn, pg.keyOut
FROM payment_gateway AS pg 
    INNER JOIN reseller_shop AS rs ON  rs.id = pg.resellerShopId
WHERE pg.type = 'paymentGatewayOgone'

or:

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

Upvotes: 0

Related Questions