Safely update SKU from DB Magento2
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ margin-bottom:0;
}
In a store of mine, built on Magento 2.1.x I need to perform a bulk update on product SKUs.
I am thinking about doing an UPDATE directly on the DB, on the catalog_product_entity table, since I noticed that the SKU information is stored in this table.
Is it safe doing it? The SKU information is contained in other tables?
magento-2.1 database sql sku
edited Sep 28 '17 at 11:15
Siarhey Uchukhlebau
9,94693058
asked Sep 28 '17 at 9:57
gianis6gianis6
7301433
bumped to the homepage by Community♦ 2 hours ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
add a comment |
In a store of mine, built on Magento 2.1.x I need to perform a bulk update on product SKUs.
I am thinking about doing an UPDATE directly on the DB, on the catalog_product_entity table, since I noticed that the SKU information is stored in this table.
Is it safe doing it? The SKU information is contained in other tables?
magento-2.1 database sql sku
edited Sep 28 '17 at 11:15
Siarhey Uchukhlebau
9,94693058
asked Sep 28 '17 at 9:57
gianis6gianis6
7301433
bumped to the homepage by Community♦ 2 hours ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
Are you sure you want to use SQL instead of Magento API? Have a look here: fishpig.co.uk/magento/tutorials/direct-sql-queries
– Els den Iep
Sep 28 '17 at 13:58
add a comment |
In a store of mine, built on Magento 2.1.x I need to perform a bulk update on product SKUs.
I am thinking about doing an UPDATE directly on the DB, on the catalog_product_entity table, since I noticed that the SKU information is stored in this table.
Is it safe doing it? The SKU information is contained in other tables?
magento-2.1 database sql sku
edited Sep 28 '17 at 11:15
Siarhey Uchukhlebau
9,94693058
asked Sep 28 '17 at 9:57
gianis6gianis6
7301433
In a store of mine, built on Magento 2.1.x I need to perform a bulk update on product SKUs.
I am thinking about doing an UPDATE directly on the DB, on the catalog_product_entity table, since I noticed that the SKU information is stored in this table.
Is it safe doing it? The SKU information is contained in other tables?
magento-2.1 database sql sku
magento-2.1 database sql sku
edited Sep 28 '17 at 11:15
Siarhey Uchukhlebau
9,94693058
asked Sep 28 '17 at 9:57
gianis6gianis6
7301433
edited Sep 28 '17 at 11:15
Siarhey Uchukhlebau
9,94693058
asked Sep 28 '17 at 9:57
gianis6gianis6
7301433
edited Sep 28 '17 at 11:15
Siarhey Uchukhlebau
9,94693058
edited Sep 28 '17 at 11:15
Siarhey Uchukhlebau
9,94693058
edited Sep 28 '17 at 11:15
Siarhey Uchukhlebau
9,94693058
9,94693058
asked Sep 28 '17 at 9:57
gianis6gianis6
7301433
asked Sep 28 '17 at 9:57
gianis6gianis6
7301433
asked Sep 28 '17 at 9:57
gianis6gianis6
7301433
7301433
bumped to the homepage by Community♦ 2 hours ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
bumped to the homepage by Community♦ 2 hours ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
Are you sure you want to use SQL instead of Magento API? Have a look here: fishpig.co.uk/magento/tutorials/direct-sql-queries
– Els den Iep
Sep 28 '17 at 13:58
add a comment |
Are you sure you want to use SQL instead of Magento API? Have a look here: fishpig.co.uk/magento/tutorials/direct-sql-queries
– Els den Iep
Sep 28 '17 at 13:58
Are you sure you want to use SQL instead of Magento API? Have a look here: fishpig.co.uk/magento/tutorials/direct-sql-queries
– Els den Iep
Sep 28 '17 at 13:58
Are you sure you want to use SQL instead of Magento API? Have a look here: fishpig.co.uk/magento/tutorials/direct-sql-queries
– Els den Iep
Sep 28 '17 at 13:58
add a comment |
1 Answer
1
active
oldest
votes
You can check which tables has column with sku name using next query:
SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME LIKE '%sku%'
Result should look like this:
Then you can safely update the sku in all tables, but be careful - some columns can have foreign keys. You can disable the foreign key check before update, and enable it back after:
SET foreign_key_checks = 0;
# make some changes here ...
SET foreign_key_checks = 1;
or manually check all tables before the update.
Note: this solution will affect an only columns which potentially store a sku, but magento has some columns, like conditions_serialized where product sku can be used, where you should make changes manually.
Here is example from the Shipping Suite module, where sku used in the conditions_serialized column:
To obtain a list of potentially affected columns you can use this queries:
SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME LIKE '%serializ%' ORDER BY `COLUMNS`.`TABLE_SCHEMA` DESC
SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME LIKE '%cond%' ORDER BY `COLUMNS`.`TABLE_SCHEMA` DESC
Where '%serializ%' means part of the word serialized, '%cond%' - part of the word conditions.
Example result:
answered Sep 28 '17 at 10:57
Siarhey UchukhlebauSiarhey Uchukhlebau
9,94693058
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "479"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fmagento.stackexchange.com%2fquestions%2f195073%2fsafely-update-sku-from-db-magento2%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
You can check which tables has column with sku name using next query:
SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME LIKE '%sku%'
Result should look like this:
Then you can safely update the sku in all tables, but be careful - some columns can have foreign keys. You can disable the foreign key check before update, and enable it back after:
SET foreign_key_checks = 0;
# make some changes here ...
SET foreign_key_checks = 1;
or manually check all tables before the update.
Note: this solution will affect an only columns which potentially store a sku, but magento has some columns, like conditions_serialized where product sku can be used, where you should make changes manually.
Here is example from the Shipping Suite module, where sku used in the conditions_serialized column:
To obtain a list of potentially affected columns you can use this queries:
SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME LIKE '%serializ%' ORDER BY `COLUMNS`.`TABLE_SCHEMA` DESC
SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME LIKE '%cond%' ORDER BY `COLUMNS`.`TABLE_SCHEMA` DESC
Where '%serializ%' means part of the word serialized, '%cond%' - part of the word conditions.
Example result:
answered Sep 28 '17 at 10:57
Siarhey UchukhlebauSiarhey Uchukhlebau
9,94693058
add a comment |
You can check which tables has column with sku name using next query:
SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME LIKE '%sku%'
Result should look like this:
Then you can safely update the sku in all tables, but be careful - some columns can have foreign keys. You can disable the foreign key check before update, and enable it back after:
SET foreign_key_checks = 0;
# make some changes here ...
SET foreign_key_checks = 1;
or manually check all tables before the update.
Note: this solution will affect an only columns which potentially store a sku, but magento has some columns, like conditions_serialized where product sku can be used, where you should make changes manually.
Here is example from the Shipping Suite module, where sku used in the conditions_serialized column:
To obtain a list of potentially affected columns you can use this queries:
SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME LIKE '%serializ%' ORDER BY `COLUMNS`.`TABLE_SCHEMA` DESC
SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME LIKE '%cond%' ORDER BY `COLUMNS`.`TABLE_SCHEMA` DESC
Where '%serializ%' means part of the word serialized, '%cond%' - part of the word conditions.
Example result:
answered Sep 28 '17 at 10:57
Siarhey UchukhlebauSiarhey Uchukhlebau
9,94693058
add a comment |
You can check which tables has column with sku name using next query:
SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME LIKE '%sku%'
Result should look like this:
Then you can safely update the sku in all tables, but be careful - some columns can have foreign keys. You can disable the foreign key check before update, and enable it back after:
SET foreign_key_checks = 0;
# make some changes here ...
SET foreign_key_checks = 1;
or manually check all tables before the update.
Note: this solution will affect an only columns which potentially store a sku, but magento has some columns, like conditions_serialized where product sku can be used, where you should make changes manually.
Here is example from the Shipping Suite module, where sku used in the conditions_serialized column:
To obtain a list of potentially affected columns you can use this queries:
SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME LIKE '%serializ%' ORDER BY `COLUMNS`.`TABLE_SCHEMA` DESC
SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME LIKE '%cond%' ORDER BY `COLUMNS`.`TABLE_SCHEMA` DESC
Where '%serializ%' means part of the word serialized, '%cond%' - part of the word conditions.
Example result:
answered Sep 28 '17 at 10:57
Siarhey UchukhlebauSiarhey Uchukhlebau
9,94693058
You can check which tables has column with sku name using next query:
SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME LIKE '%sku%'
Result should look like this:
Then you can safely update the sku in all tables, but be careful - some columns can have foreign keys. You can disable the foreign key check before update, and enable it back after:
SET foreign_key_checks = 0;
# make some changes here ...
SET foreign_key_checks = 1;
or manually check all tables before the update.
Note: this solution will affect an only columns which potentially store a sku, but magento has some columns, like conditions_serialized where product sku can be used, where you should make changes manually.
Here is example from the Shipping Suite module, where sku used in the conditions_serialized column:
To obtain a list of potentially affected columns you can use this queries:
SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME LIKE '%serializ%' ORDER BY `COLUMNS`.`TABLE_SCHEMA` DESC
SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME LIKE '%cond%' ORDER BY `COLUMNS`.`TABLE_SCHEMA` DESC
Where '%serializ%' means part of the word serialized, '%cond%' - part of the word conditions.
Example result:
answered Sep 28 '17 at 10:57
Siarhey UchukhlebauSiarhey Uchukhlebau
9,94693058
edited Sep 28 '17 at 11:12
answered Sep 28 '17 at 10:57
Siarhey UchukhlebauSiarhey Uchukhlebau
9,94693058
answered Sep 28 '17 at 10:57
Siarhey UchukhlebauSiarhey Uchukhlebau
9,94693058
answered Sep 28 '17 at 10:57
Siarhey UchukhlebauSiarhey Uchukhlebau
9,94693058
9,94693058
add a comment |
add a comment |
Thanks for contributing an answer to Magento Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fmagento.stackexchange.com%2fquestions%2f195073%2fsafely-update-sku-from-db-magento2%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Are you sure you want to use SQL instead of Magento API? Have a look here: fishpig.co.uk/magento/tutorials/direct-sql-queries
– Els den Iep
Sep 28 '17 at 13:58