quote:

---

Originally posted by d confz

Hello..

Can I update only a single row on the database?
For example I have the table Product:
product_name ------- qty -------- price
Monggol ------------- 12 ---------- 7
Paper --------------- 10 ---------- 22
Monggol ------------- 89 ---------- 51

The user inputs a productname, qty and price. Then I need to update a product's qty where product name is equal to the product name from the user.

Using the code:
UPDATE Product SET qty = '"+number+"' WHERE product_name = '"+prodname+"';
(number and prodname is the value from the user)

This will update the qty of all the product_name that is equal to the prodname. But what I need is to update only the first row. I cannot use the price because the price that is input by the user is different from the price in the database, it is for checking the earnings.

Is it possible for me to do it?

Thanks a lot..

---

UPDATE t
SET t.qty =@qty 
FROM (SELECT TOP 1 *
      FROM Product
      WHERE product_name = @productname
      ORDER BY DateField DESC) t

quote:

---

Originally posted by harsh_athalye

Define what do you mean by first row.

In relational world, terms first and last are meaningless unless you specify record position relative to some sort order.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

---

quote:

---

Originally posted by jackv

Use the following , replacing 'Prod1' with your incoming variable

CREATE TABLE Products
(
ID INT,
Product_Name VARCHAR(20),
Qty INT,
Price DECIMAL(10,2)
)

INSERT INTO Products
SELECT 1,'Prod1',2,2.5
UNION
SELECT 2,'Prod2',3,6.5
UNION
SELECT 3,'Prod1',3,6.5

UPDATE Products SET qty = 7 WHERE product_name = 'Prod1' AND
ID IN (SELECT TOP 1 ID FROM Products as t2 WHERE t2.product_name = 'Prod1')


SELECT * FROM Products


DROP TABLE Products


Jack Vamvas
--------------------
Search IT jobs from multiple sources- http://www.ITjobfeed.com

---

quote:

---

Originally posted by visakh16

quote:

---

Originally posted by d confz

Hello..

Can I update only a single row on the database?
For example I have the table Product:
product_name ------- qty -------- price
Monggol ------------- 12 ---------- 7
Paper --------------- 10 ---------- 22
Monggol ------------- 89 ---------- 51

The user inputs a productname, qty and price. Then I need to update a product's qty where product name is equal to the product name from the user.

Using the code:
UPDATE Product SET qty = '"+number+"' WHERE product_name = '"+prodname+"';
(number and prodname is the value from the user)

This will update the qty of all the product_name that is equal to the prodname. But what I need is to update only the first row. I cannot use the price because the price that is input by the user is different from the price in the database, it is for checking the earnings.

Is it possible for me to do it?

Thanks a lot..

---

i think this is what you want

UPDATE t
SET t.qty =@qty 
FROM (SELECT TOP 1 *
      FROM Product
      WHERE product_name = @productname
      ORDER BY DateField DESC) t

This will update the quantity of latest (most recent) record of that product passed by user with quantity value given. If this is not what you require, you need to specify your requirement for first record.

---

quote:

---

Originally posted by harsh_athalye

Again what do you mean by "first data in the database"?

There could be hundreds of records matching user input, which one would you consider first?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

---

quote:

---

Originally posted by d confz

quote:

---

Originally posted by harsh_athalye

Again what do you mean by "first data in the database"?

There could be hundreds of records matching user input, which one would you consider first?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

---

I'm using the FIFO.. So it is the first product_name that is equal to prodname (value input by the user)..

---

quote:

---

Originally posted by madhivanan

In Mysql, you should use LIMIT function

ex

Select columns from table
order by somecol
LIMIT 10

Also post mysql related questions at www.mysql.com

Madhivanan

Failing to plan is Planning to fail

---