Questions tagged [sql]

Questions about using raw SQL queries. For questions about the database in general, use the database tag instead, for questions about the Magento database access layer use the appropriate tag, like: eav, model, resource-model, collections, install-script

Filter by
Sorted by
Tagged with
0
votes
1answer
12 views

Is there any way to get enabled and disabled simple product of configurable product in Magenoto 2?

Is there any way to get enabled and disabled simple product of configurable product in Magento 2. I need to get all id of enable and disabled product by using SQL query or object manager approach. Can ...
0
votes
0answers
7 views

Getting access of records in db, SQL file size is 18+GB while export

I have exported my database in SQL file using the MySQLDump command from the SSH terminal. My store contains the data as below: No. of products below 40,000. No. of customer below 3000. No. of ...
0
votes
0answers
23 views

Magento 2: How do I get the tables that contains data of Categories, Products, Customers, Orders, Product Reviews Product Attributes and its values?

I want to export the tables that contain the data of Categories, Products, Customers, Orders, Product Attributes and its values (along with the custom attributes) Product Reviews from my Magento 2 ...
0
votes
0answers
18 views

SQL error on Database Import, while setting up staging site

I have been trying to import a database to clone a production site that is working and I am receiving this error when trying to import the database. Error Static analysis: 2 errors were found during ...
0
votes
0answers
13 views

magento brand showing error

i am getting error this error Exception #0 (Zend_Db_Statement_Exception): SQLSTATE[42S22]: Column not found: 1054 Unknown column 'e.manufacturer' in 'where clause', query was: SELECT FLOOR((ROUND((e....
0
votes
2answers
27 views

Magento: Reduce product weight by 1000x for all products

We migrated from Magento 1 to Magento 2. All weight values were added in grams in Magento 1. In Magento 2 the weight setting is in KG. So all product weights are 1000x too much. What SQL query can I ...
0
votes
0answers
25 views

Magento 2 How to set url key for default store view

Magento 2.3.5 version How to set URL key for default store view? Example - All store view URL-key - xyz-test which is correct Default Store View URL-key - abc-test it is not correct I want to set the ...
0
votes
0answers
14 views

Set country of origin

there is a task to create an attribute country of origin and fill it in in accordance with the attribute country of manufacture, for example (Germany -> DE), and the question arose where to get the ...
1
vote
1answer
29 views

Assign orders to customers where the emails are matching with a SQL query

I want to reassign existing orders to customers after deleting all customers and importing them into Magento 2 from a csv. The sales_order.customer_id values are not matching with the correct customer ...
0
votes
0answers
18 views

Magento 2 getting products use sql

I'm trying to get all products using Magento SQL. I just need to do some tweaks. If the product has a thumbnail, it should show its value and if it is null, it should show my custom text. My code ...
2
votes
0answers
62 views

Magento 2 : Get product name using mysql query

I am using Magento ver. 2.4.2 Is their any way we can get product attributes by id using mysql query ? I also need some custom attributes for product in same query THE BELOW SQL QUERY WORKS BUT GET ...
0
votes
0answers
42 views

Magento 2: Get used and unused attributes

I want to get list of attributes (not only products or categories) that are used and that are not use. I try this Delete unused attribute values one but it's only for product attribute, this is for ...
0
votes
1answer
25 views

SQL Syntax error when getting collection on listing

I've created my own module for managing leaflets in home page and i wanted to have a listing with existing leaflets. I've created UI_Component for listing (by Magicento) but when I go to listing page ...
1
vote
1answer
84 views

Why does it matter in which order I add attributes to a filter?

Compare this code $productIdQuery = $this->productCollectionFactory->create() ->addAttributeToSelect('customer_group') ->addAttributeToFilter([ ['attribute' ...
0
votes
1answer
670 views

SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: After Updating a Module

On My site i am running old version of Advanced Product Options Suite Extension for Magento 2 by mageworx. I just updated the Advanced Product Options Suite Extension into latest version. I tired ...
0
votes
0answers
41 views

setup:upgrade Magento Integrity constraint violation

I've enabled the module Magento_AdminNotification and ran php bin/magento setup:upgrade which threw the SQL error. SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 'Stoßdämpfer-1' ...
0
votes
1answer
37 views

How to import 3 tables dump into database using script?

I want to include 3 tables SQL dump into that database, how to add it by using a custom module or script or any best practice.
0
votes
0answers
44 views

Hiding Thumbnail images from Gallery - SQL Query

A while ago I had a bad import in a store and hundreds of thousands of thumbnail images got set to show up on product page in Gallery. Most of the time, you don't want the gallery to show thumbnail ...
0
votes
0answers
11 views

disable configurable products if the associated products have quantity 0

Magento 1.9.x I have to enable the backorder. How can I disable all CONFIGURABLE products that have associated products with quantity 0? Is there a SQL query that allows me to do this? (via admin ...
0
votes
0answers
25 views

SQl Query to Get Product Magento2

I want to check how many product added and update daily on my catalog using sql query and i want following columns Sku Brand Category title product ulr image url price what sql i will write in mysql ...
1
vote
2answers
645 views

How to get all products info from database by SQL Query in Magento 2.3?

I want to get the below data of all products by SQL Query. SKU Product name Website Attribute set Quantity per source Sellable quantity visibility Price Status I have used this extension: https://...
0
votes
0answers
38 views

magento 2 Cannot add foreign key constraint while importing admin_user table

I'm trying to export admin_user table from one database and importing it into another database. But it shows the below issue. #1215 - Cannot add foreign key constraint I have also disabled foreign ...
0
votes
1answer
47 views

SQL is_in_stock field not updating to Website

I am updating is_in_stock field in cataloginventory_stock_item as follows: UPDATE cataloginventory_stock_item ci SET ci.is_in_stock = CASE WHEN ci.qty = 0 THEN 0 WHEN ci.qty > 0 THEN 1 END WHERE ...
0
votes
0answers
12 views

Magento 2 getSelect with columns only showing one record in admin grid

I have a collection like below. protected function _initSelect() { $this->addFilterToMap('entity_id', 'main_table.entity_id'); $this->addFilterToMap('increment_id', 'main_table....
0
votes
1answer
27 views

Re-indexed procedure in Magento 2

If i update 10K products and re-indexed it first time it will re-indexed all. But if i update 10 more product so it will require re-index so my question is is it re-index all the products from ...
0
votes
1answer
35 views

How to Make Indexing process Async. in Magento 2

I am using Indexing process in Magento 2. I have 60 Million products and when i run indexer it just struck in processing from past 4 days. When i check indexer status on sow processlist it will run ...
1
vote
4answers
531 views

Where is URL Key information stored in database?

I'm using a mass import extension to import product URL Keys for a Magento 2.3.0 site which recently went live. The URL key information in the backend product detail is correctly updated but Magento ...
0
votes
3answers
47 views

I want to fetch Product Names on behalf of comma separated SKU's

I want to fetch Product Names on behalf of comma separated SKU's i.e. sku1,sku2,sku3 and the expected result is Name1,Name2,Name3
0
votes
1answer
78 views

How to export products with tier prices directly via database using SQL?

I want to export these data from my Magento 2.4: Status Product Name SKU Price Advanced Pricing Quantity How could I do it via SQL?
1
vote
1answer
112 views

Magento 2 - How to get data from table customer_log?

I need to get the value for last_login_at for the current customer, but this seems to be impossible without the use of SQL. Current Solution (SQL): /** * @var \Magento\Framework\App\...
0
votes
4answers
116 views

how to use date() in magento query?

I want to filter some orders based on the date range so in SQL i use this query SELECT * FROM `sales_order` WHERE date(created_at) >= '2020-12-02' AND (date( created_at) <= '2020-12-...
0
votes
1answer
43 views

How do I change not using ObjectManager directly? and use Dependency injection?

How do I change not using ObjectManager directly? and use Dependency injection? what about my SQL query? My code is work, but seems not in good Magento style, anyone can help to correct me? I am ...
0
votes
1answer
204 views

SQL query to get products enabled, in stock without images?

I'm running a Magento 2.3 and I would like to run a query in my database to export some products data that I don't have natively via the admin panel, the attributes are: Status Stock Images How ...
0
votes
1answer
28 views

Create a custom magento cron job

Is it possible to create a custom magento cron job. The task I want it can be done by run sql query using php to process some logic of the sql result send out email to customer service. cron job ...
0
votes
1answer
194 views

How to change product status from sql using SKU

I need to disable and enable a huge quantity of products filtering by sku and the easiest solution seem to be through Sql query but i have some issues to find the right code For example i need to ...
0
votes
1answer
24 views

Can not display all column from a table

I have a table name my_cars. There are 4 column in my table. I create collection, model, recourse model,controller and block. And i can display data from my table. But all column of data not ...
0
votes
1answer
86 views

Invalid security or form key. Please refresh the page. UI Form magento 2

I`m using this plugin , but i have another table structure. I can add new item to database bur if i edit item i get err Invalid security or form key. Please refresh the page. My table structure id, ...
0
votes
1answer
171 views

How to filter customer by custom attribute using SQL query?

The zd_user_id is our customer custom attribute. $customerCollection = $this->customerFactory->create(); $customers = $customerCollection->addAttributeToFilter( [ ['attribute' =&...
0
votes
1answer
171 views

Notice: Array to string conversion in shipping delivery on magento 2

I upgraded from magento 2.2.2 to 2.2.11 since I have an error when selecting a shipping address. I get the error: "Notice: Array to string conversion in mywebsite/vendor/magento/framework/DB/...
0
votes
1answer
979 views

How to get custom attribute value with product id of enable product with SQL query in Magento 2?

How to get custom attribute value with product id of enabled product with SQL query in Magento 2? I am using SELECT entity_id,value FROM catalog_product_entity_varchar WHERE attribute_id = 150 But it ...
0
votes
1answer
112 views

Magento 2 delete Meta Name Meta Title Meta Description ALL products

Magento 2 delete Meta Name Meta Title Meta Description ALL products Please can someone let me know if its possible to delete the Meta Values for all products in my store These were added during import ...
0
votes
1answer
196 views

How to get SQL query for product insert in Magento 2?

How to get SQL query for product insert in Magento 2 ? I have multiple products around 10 Lakhs so i want to insert it using SQL query. But how will i get the insert query of the product ? Any Help is ...
0
votes
1answer
63 views

Move house number from street 1 into street 0 for existing address in db - Magento 2.3.5

At the moment we use the street 1 field for the house number in our Magento 2.3.5 store. But we want to revert this and only use 1 street field for both the street and house number. So we need to move ...
0
votes
1answer
237 views

How to get customer and company data in a CSV file from MySQL?

I'm running a Magento Commerce Cloud B2B, and I need to get a list of these data below. Customer ID Customer Name Company Name Company User Agreement As I can't export a file with these pieces of ...
0
votes
1answer
479 views

Magento2 - php bin/magento setup:upgrade - Access denied for user 'user'@'localhost' (using password: NO)

Fairly new to Magento, I am trying to develop a custom module and have run the CLI command php bin/magento setup:upgrade. However I am getting the following error; SQLSTATE[HY000] [1045] Access denied ...
0
votes
2answers
577 views

Column not found: 1054 Unknown column 'cat_index_position' in 'order clause'

I am getting a SQL error on a few category pages after I upgraded to Magento 2.4.0. I do not have any errors during re-indexing. Flat tables are also disabled. Also, there is no custom code for the ...
0
votes
2answers
116 views

Magento 2 - Delete all products with ID 140.000 and higher

We have a lot of duplicate products in our Magento 2 store, that we need to remove. Now I am looking for a way or a sql query to remove all products from id 140.000 or higher. So all products from 1 ...
1
vote
1answer
1k views

Magento 2.3 - How to bulk import attributes values?

What is the best practice to bulk import attributes values. How to do it by hit tables?
1
vote
1answer
26 views

I need to set the 'OR' condtion for last where clause but it is setting as AND how can change in magento 2.3

SQL need to change AND condtion to OR in below code public function getOrders() { $po_number = "%"."A"."%"; if (!($customerId = $this->...
0
votes
3answers
87 views

Script to round all product prices in database to 2 decimal places

What is the best approach to go through all products PRICES (price, final price, promo price, group price ...) and round them to 2 decimal places. for example: 28,1200 -> 28,1200 28,1255 -> 28,...

1
2 3 4 5
13