Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2003-02-24 : 19:33:32
|
| Ann writes "I have two tables. One is a list of products and one is a list of prices for each product (these are small files). There can be more than one price for a product. There could also be no prices for a product. I need to create a stored procedure which will return all products and the first price for each product. I tried doing this using JOINS but it kept repeating a product for each selling price or not returning the product if there was no price. I decided to create a temporary table. I first select all the products and update the temporary table. The temporary table has the product code and description. I have a price field which I set to zero initially. Now I want to go through this table, read the first price record and update the price. So I'm using a cursor. I create a cursor, using the FETCH statement to get each row from the temporary table. Then I read the first price record from the prices file but I can not figure out how to update the row in the temporary table.Can I update the temporary table. Or is there a better way to do what I want to do.Operating System: Windows 2000 ServerSQL: Microsoft SQL Server 2000 ver: 8.00.0000" |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-02-24 : 20:20:39
|
| select p.Product_name,(select top 1 Price from Prices pc where pc.Product_id = p.id order by dte desc)from Products==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
|
|
|
Cursor and Updates