Possibility of foreach in SQL?

Question

I'm currently having a problem in SQL.

I have two tables. The first one contains an int value and the int ID of an account and the second one contains some userdata and the value, I want to update.

table:
```
TABLE (INT AccountID, INT ValueToAdd)
```

table:

TABLE (INT AccountID, INT ValueToBeUpdated, ...)

I want SQL to look through the 1. table which AccountID it needs from the 2. table and should then update the ValueToBeUpdated value where the 1.table.AccountID = 2.table.AccountID

I hope you get what I mean. I didn't find a way to do this in a set-orientated language like SQL yet, as I cant do foreach loops through one of the tables like here:

foreach (row in 2.table)
{
  if (row.AccountID IN 1.table.AccountID)
  {
     UPDATE row SET ValueToBeUpdated = (SELECT ValueToAdd FROM 1.table WHERE AccountID = row.AccountID)
  }
}

I hope you can help me out :)

You are thinking of a looping structure. Think of SQL more like set-based operations. For this one, you can actually join to other tables within an `UPDATE` command. Check this closely duplicated answer: http://stackoverflow.com/questions/2334712/update-from-select-using-sql-server?rq=1 — TTeeple, Feb 03 '14 at 16:41
`WHILE` loops and iterative solutions using cursors are possible in `sql` but should always be avoided if a set based solution is available. — whytheq, Feb 03 '14 at 16:51

score 4 · Accepted Answer · edited May 23 '17 at 10:31

4

Forget looping, You could do like this for your case :

UPDATE
    row
SET
    r.ValueToBeUpdated.col1 = t.ValueToAdd
FROM
    row r
INNER JOIN
    table t
ON
    t.AccountID = r.AccountID

References

Why are relational set-based queries better than cursors?

Understanding “Set based” and “Procedural” approaches in SQL

edited May 23 '17 at 10:31

Community

1
1

answered Feb 03 '14 at 16:46

hsuk

6,770
13
50
80

Okay I have a complex select query to data of the 1.table cause that results of 3 INNER JOINs already. But I made myself a temporary table now where I first stored that data in and now you suggestion works. Thanks :) – Vinz Feb 03 '14 at 17:08

Pragnesh Khalas · Answer 2 · 2014-02-03T17:06:50.710

0

It is easily execute without using foreach loop.

UPDATE table2 SET ValueToBeUpdated = a.ValueToAdd
FROM table2 b INNER JOIN table1 a 
ON a.AccountID = b.AccountID

edited Feb 03 '14 at 17:06

answered Feb 03 '14 at 16:44

Pragnesh Khalas

2,908
2
13
26

Why not use a JOIN? This is artificially hard to understand. It is like a code obfuscation contest entry. – usr Feb 03 '14 at 16:57

Possibility of foreach in SQL?

2 Answers2