Proper way to update SQL table without using loops

Question

I want to grab a list of values from an SQL table, and then run a query using each of these values to update other fields in the same table. In another language, I'd use a for loop for this, but I've read that looping operations should be avoided/aren't supported in SQL.

Conceptually (obviously the code isn't valid), this is what I'm trying to achieve:

my_list = SELECT item_id
          FROM my_table
          WHERE value = "my_value"
          AND field_id = 1

for x in my_list:
    UPDATE my_table
    SET meta_value = "my_value"
    WHERE field_id = 2
    AND item_id = x

What's the proper way to achieve this is SQL please?

sagi · Accepted Answer · 2016-02-14 09:55:45Z

2

Try this update with join statement:

update My_Table t
JOIN My_Table s on(t.item_id = s.item_id
                   and t.field_id = 2
                   and s.field_id = 1)
set t.meta_value = 'my_value'
where s.value = 'MY_VALUE'

answered Feb 14, 2016 at 9:55

sagi

40.6k7 gold badges66 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ed Heal · Accepted Answer · 2016-02-14 10:01:47Z

1

this should work without using loops:

  UPDATE my_table 
  SET meta_value = "my_value" 
  WHERE field_id = 2 AND item_id IN
  (
    SELECT item_id
    FROM my_table
    WHERE value = "my_value"
    AND field_id = 1
);

edited Feb 14, 2016 at 10:01

Ed Heal

60.3k18 gold badges91 silver badges137 bronze badges

answered Feb 14, 2016 at 9:55

user5924765

4 Comments

user1684046 Over a year ago

Thanks, but this throws an error:#1093 - You can't specify target table 'my_table' for update in FROM clause

user5924765 Over a year ago

Hmmm, I tried this with an Oracle database before suggesting this solution and it worked

user5924765 Over a year ago

Obviously, you're using a MySQL datbase, so you should go for @hasumedic's answer

user1684046 Over a year ago

Probably my fault for not being specific. Thanks anyway.

hasumedic · Accepted Answer · 2016-02-14 09:57:32Z

0

You should be able to join the table with a subset of itself:

UPDATE my_table t1
INNER JOIN (SELECT item_id FROM my_table WHERE value = "my_value" AND field_id = 1) t2 ON t1.item_id = t2.item_id
SET t1.meta_value = "my_value"
WHERE t1.field_id = 2

answered Feb 14, 2016 at 9:57

hasumedic

2,17712 silver badges17 bronze badges

Collectives™ on Stack Overflow

Proper way to update SQL table without using loops

3 Answers 3

Comments

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related