1

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
Community
  • 1
  • 1
Mike
  • 333
  • 4
  • 19
  • can it be this: UPDATE eav_attribute_option,tbl2 SET eav_attribute_option.sort_order = tbl2.iterator WHERE eav_attribute_option.option_id = tbl2.option_id I'm just having trouble with the "tbl2" part... – Mike May 16 '12 at 14:49
  • 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)) tbl2 SET eav_attribute_option.sort_order = tbl2.iterator WHERE eav_attribute_option.option_id = tbl2.option_id – Mike May 16 '12 at 15:48

2 Answers2

1

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.

somnath
  • 1,337
  • 1
  • 9
  • 13
  • Thanks. I managed to get it to work with the query I posted as a comment. As for the views, I was under the impression they only represent a load on the server when they are executed, i.e., just the same as executing the query instead of calling the name of the view. Anyway, I wanted to avoid views so that the changes in the mysql server remain at a minimum. I'd rather execute the sql command every once in a while to keep the entries organized. Thanks – Mike May 16 '12 at 15:46
  • Amazing solution! I didnt have a query tool with me to try that when I posted the safe solution. Great work! – somnath May 16 '12 at 15:52
1
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
Mike
  • 333
  • 4
  • 19