For Each equivalent in SQL

Question

I have a table, tblDocs that has a few columns:

DocName varchar(50)
DocLocation int
Active int
DocID int

All entries in the table have a DocName and DocLocation. Active and DocID are blank.

What I need to do is for each row in tblDocs I need to check the value of DocLocation and based on that value I update tblDocs, setting the Active and DocID columns. I was thinking that I would use a CASE WHEN but I'm not sure - I dont know sql very well. If i were using c# or vb.net it would be the equivalent of a for each loop.

foreach row as DBRow in tblDocs
    row.active = 1
    row.docID = ID
next

How do you do this for SQL Server 2005?

UPDATE

From a couple of responses below it sounds like I will use an UPDATE Statement. So I could accomplish this by doing something like

UPDATE tblDocs
SET docID = 
    CASE DocLocation
        WHEN 1 THEN --do stuff
        WHEN 3 THEN --do other stuff

and this would go through ALL the rows of tblDocs and update the DocID to be based on DocLocation?

Answer 1

The power of SQL is that you don't need to write loops; you just tell it the things you want to update. For example, if you wanted to set all rows to be active, you'd just do...

UPDATE tblDocs
SET Active = 1

You want this to depend on the location, so why not...

UPDATE tblDocs
SET Active = 1, DocID=42
WHERE DocLocation = 7

(Substituting, of course, appropriate values.) It may be possible to write a single query to update everything, but that will depend on what, specifically, you need to change.

Answer 2

SQL is a language for data manipulation, don't think about it in terms of traditional control flow structures like loops. Allways try to use the standard SQL operators like INSERT, UPDATE or DELETE.

It depends on what values will you use to update the table, but surely you can do it with a simple update clause.

For example:

UPDATE tblDocs SET 
  Active = @activeValue,
  DocID = @docIDValue
WHERE DocLocation = @someLocation

You could do something similar if your values for the update are coming from another table.

EDIT Yes, you can do that with a CASE statement.

Answer 3

An Update clause will apply on all the rows that satisfy a condition expressed in the where segment

for example: -- Will update all rows putting docId = 0 UPDATE TblDocs SET docId = 0

-- Will update all rows satisfying locationId = 1 putting docId = 0
UPDATE TblDocs SET docId = 0 WHERE locationId = 1

-- Will update all rows satisfying locationId = 1 putting docId = locationId + 1
UPDATE TblDocs SET docId = locationId + 1 WHERE locationId = 1

Meaning, in the set segment, you can use any logic neccesary to accomplish your task

You can even make an update based on another set of data, maybe in other table

UPDATE TblDocs
SET TblDocs.docId = TblLocations.LocId
FROM TblLocations
WHERE TblLocations.docId = TblDocs.docId

you can use a cursor, but be aware that almost always you can get a solution using set logic.

here is how to create a cursor

DECLARE @DocName CHAR(10), @DocLocation INT, @Active BIT, @DocID INT

DECLARE C CURSOR 
FOR SELECT DocName, DocLocation, Active, DocID FROM TblDocs

OPEN C

FETCH NEXT FROM C INTO @DocName, @DocLocation, @Active, @DocID
WHILE @@FETCH_STATUS = 0 BEGIN

    -- do your logic here
    FETCH NEXT FROM C INTO @DocName, @DocLocation, @Active, @DocID
END
CLOSE C
DEALLOCATE C

Answer 4

Use a cursor: http://www.mssqltips.com/tip.asp?tip=1599

When you need to check a value in that row, and then update it based on something I find that cursors work best. However beware, there is performance issues if the table is really large or any joins are done in a cursor.

Answer 5

If i were using c# or vb.net it would be the equivalent of a for each loop.

The trick here is that C# and VB.Net (and javascript, c++, and any other language you're likely used to) are procedural languages. SQL is a declarative language. You describe operations that act on an entire set a time.

With that in mind, you want to describe an operation that will "check the value of DocLocation and based on that value I update tblDocs, setting the Active and DocID columns." And to do that you use an UPDATE query and case statements:

UPDATE tblDocs
SET 
  Active = CASE WHEN DocLocation = 'something' THEN 1 ELSE 0 END,
  DocID = CASE WHEN DocLocation = 'something' THEN NULL ELSE DocID END

Just a note that this sample also demonstrates how you can use the CASE statement to unset a value (set it back to null) or even leave it alone by assigning it itself. It's also worth nothing that if you really expect to leave a lot of the values alone, you should do that with a WHERE clause instead.

Answer 6

That concept does not exist in SQL (well, almost true). You should read up on the basic concepts of SQL before you try to use it, it's quite different to procedural programming.