CODEWITHSUNDEEP
mysqlmagento
Mike
Mike

Reputation: 331

update column with values obtained from joining two tables in MySQL

There's a lot of info about joining two tables, however I'm not interested in a Select, but an Update (this one also didn't help: Update a column in a table with values from two other tables).

I need to order attributes in Magento. The way I'm planning to do it is this: There are two relevant tables (eav_attribute_option and eav_attribute_option_values). They are joined by option_id. I need to update the sort_order field in the eav_attribute_option table according to the alphabetical order in the eav_attribute_option_values table (value column).

What I have so far is this:

SELECT *
FROM eav_attribute_option_value, eav_attribute_option
WHERE eav_attribute_option_value.option_id = eav_attribute_option.option_id    
ORDER BY value

with which I obtain:

value_id option_id store_id value    option_id_1  attribute_id  sort_order
13534      5681     0   ADULT               5681          131           0
13543      5710     0   Yellow              5710          134           0
13547      502      0   Yellow - 10A         502          127           0
13548      3001     0   Yellow - 120cm      3001          127           0
13549      503      0   Yellow - 12A         503          127           0

with the following query I see what I need:

SELECT t.*, @i:=@i+1 AS iterator
FROM (SELECT value, eav_attribute_option.option_id
FROM eav_attribute_option_value, eav_attribute_option
WHERE eav_attribute_option_value.option_id = eav_attribute_option.option_id 
ORDER BY value) t,(SELECT @i:=0) foo

I get this:

value           option_id   iterator
ADULT           5681        1
Yellow          5710        2
Yellow - 10A    502         3
Yellow - 120cm  3001        4
Yellow - 12A    503         5
Yellow - 14A    504         6

The big question: How can I update the sort_order column in eav_attribute_option with the values from the "iterator" column, having option_id as the linking field?

select * from eav_attribute_option

option_id   attribute_id    sort_order
1            18             0
2            18             1
3            127            0
4            127            0
5            127            0
6            127            0

I got the "iterator" query from here: With MySQL, how can I generate a column containing the record index in a table?

edit: Here's the answer

UPDATE eav_attribute_option, (SELECT t.*, @i:=@i+1 AS iterator 
FROM 
(SELECT value, eav_attribute_option.option_id 
    FROM eav_attribute_option_value, eav_attribute_option 
    WHERE eav_attribute_option_value.option_id = eav_attribute_option.option_id 
    ORDER BY value) t,(SELECT @i:=0) x) tbl2 
SET eav_attribute_option.sort_order = tbl2.iterator 
WHERE eav_attribute_option.option_id = tbl2.option_id

Upvotes: 1

Views: 1793

Answers (2)

Mike
Mike

Reputation: 331

UPDATE eav_attribute_option, (SELECT t.*, @i:=@i+1 AS iterator 
FROM 
(SELECT value, eav_attribute_option.option_id 
    FROM eav_attribute_option_value, eav_attribute_option 
    WHERE eav_attribute_option_value.option_id = eav_attribute_option.option_id 
    ORDER BY value) t,(SELECT @i:=0) x) tbl2 
SET eav_attribute_option.sort_order = tbl2.iterator 
WHERE eav_attribute_option.option_id = tbl2.option_id

Upvotes: 1

somnath
somnath

Reputation: 1335

You need to use a stored procedure or a view. A single update command wont be able to do what you need as the iterator is generated and the combination is a tabular data that you need to access to update your eav_attribute_option table.

Using a SP create a cursor for the iterator query and loop through it to update the eav_attribute_option table's sort_order column using a simple where.

Using a view, you need to create a view for the iterator query. You can then use this view name to join the eav_attribute_option table and give the relevant update command.

I shall recommend the SP method as views are constantly updated when the data changes and puts a load on the database server.

Upvotes: 1

Related Questions