Gsyfuy

Is the Joker left-handed?

Why can't we play rap on piano?

Is "remove commented out code" correct English?

Blender 2.8 I can't see vertices, edges or faces in edit mode

Why do I get two different answers for this counting problem?

Why is consensus so controversial in Britain?

UK: Is there precedent for the governments e-petition site changing the direction of a government decision?

How to say in German "enjoying home comforts"

Famous Pre Reformation Christian Pastors (Non Catholic and Non Orthodox)

What reasons are there for a Capitalist to oppose a 100% inheritance tax?

Doing something right before you need it - expression for this?

Etiquette around loan refinance - decision is going to cost first broker a lot of money

Why are electrically insulating heatsinks so rare? Is it just cost?

Why is Collection not simply treated as Collection<?>

Theorems that impeded progress

Can one be a co-translator of a book, if he does not know the language that the book is translated into?

Why doesn't H₄O²⁺ exist?

Is it canonical bit space?

What to put in ESTA if staying in US for a few days before going on to Canada

intersection of two sorted vectors in C++

Can I use a neutral wire from another outlet to repair a broken neutral?

How do conventional missiles fly?

Is there a hemisphere-neutral way of specifying a season?

If human space travel is limited by the G force vulnerability, is there a way to counter G forces?

Safely update SKU from DB Magento2

Delete custom EAV attributes from the databaseSku field null in category_product_flat tablesDo have sales_flat* tables a lifecycle like catalog_product_flat* tables?Add multiple custom tables for for a custom admin moduleChange store id valueWhich tables can be truncated/deleted before doing a full reindex?Check Bulk SKUs against existing Product's Skus during importget Emails from specfied customer group in magento 2Delete category attribute from the databaseHow copy all Categories attributes for store view A to default store view

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ margin-bottom:0;
}

1

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

share|improve this question

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♦ 7 mins 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 | 

1

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

share|improve this question

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♦ 7 mins 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

share|improve this question

edited Sep 28 '17 at 11:15

Siarhey Uchukhlebau

9,94693058

asked Sep 28 '17 at 9:57

gianis6gianis6

7301433

share|improve this question

edited Sep 28 '17 at 11:15

Siarhey Uchukhlebau

9,94693058

asked Sep 28 '17 at 9:57

gianis6gianis6

7301433

share|improve this question

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

asked Sep 28 '17 at 9:57

gianis6gianis6

7301433

asked Sep 28 '17 at 9:57

gianis6gianis6

7301433

1 Answer
1

active

oldest

votes

0

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:

share|improve this answer

edited Sep 28 '17 at 11:12

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
});


}
});

draft saved

draft discarded

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

Name

Email

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

Name

Email

Required, but never shown

0

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:

share|improve this answer

edited Sep 28 '17 at 11:12

answered Sep 28 '17 at 10:57

Siarhey UchukhlebauSiarhey Uchukhlebau

9,94693058

add a comment | 

0

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:

share|improve this answer

edited Sep 28 '17 at 11:12

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:

share|improve this answer

edited Sep 28 '17 at 11:12

answered Sep 28 '17 at 10:57

Siarhey UchukhlebauSiarhey Uchukhlebau

9,94693058

SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME LIKE '%sku%'

SET foreign_key_checks = 0;

# make some changes here ...

SET foreign_key_checks = 1;

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

share|improve this answer

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