Tracnsact SQL Fetch Performance

Hi i want to make a stored procedure that will join 2 table and will check开发者_高级运维 some values to update. We will use FETCH to make a update process for each record.

But we are considering performance. Is this a bad idea to use FETCH?

Example:

Table1 BookID BookName

Table2 RatingID BookName BookID

We will join this two tables like this.

SELECT * FROM Table2
LEFT JOIN Table1 ON Table2.BookName = Table1.BookName 
WHERE Table2.BookID = 0

For each record which this query pulls, we will make an UPDATE to set Table1's bookID to Table2's BookID column.

Yes.

You should nearly always strive to avoid cursors in SQL Server and use set based alternatives instead.

The UPDATE statement does support JOINs to other tables.

(As usual) What Martin said.

Also, based on your description, the update would look something like this (for SQL Server):

UPDATE Table1
 set BookID = t2.BookID
 from Table1 t1
  inner join Table2 t2
   on t2.BookName = t1.BookName
 where T2.BookID = 0

• Inner joins, becasue of the where clause; with Table2.BookID = 0, Table2 rows not matched by the join but included anyway by the outer join will be discarded, as their BookID will always be NULL
• Similarly setting Table1's BookId to Table2's BookId will always set it to 0, because the where clause says "where Table2.BookId = 0". You might want to rethink this...?