CODEWITHSUNDEEP
mysqlsqlloops
user1684046
user1684046

Reputation: 1939

Proper way to update SQL table without using loops

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?

Upvotes: 0

Views: 91

Answers (3)

user5924765
user5924765

Reputation:

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

Upvotes: 1

hasumedic
hasumedic

Reputation: 2167

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

Upvotes: 0

sagi
sagi

Reputation: 40481

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'

Upvotes: 2

Related Questions