Adding data to row - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Adding data to row

Author

Topic

annas
Starting Member

36 Posts

Posted - 2008-03-20 : 22:45:29

Haii

Hopefully u guys can help me.
WHat i tring to do is to update a data.


INSERT INTO OrderItem(OrderID)
VALUES(1)
UPDATE OrderItem SET Name = 'Pie', Price = ' 2' WHERE OrderID = 1

the code work find, but what i want is just to update the last row not all rows saying that the OrderID = 1.

Example:

OrderID Name Price
1 Cookies 2
1 Bread 2
1 Pie 2 <-----------

if i just want to update the last row with OrderID = 1, can it be done

Thank U

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-03-20 : 23:34:16

What's name before updating? Try add name condition in where clause.

mayoorsubbu
Yak Posting Veteran

95 Posts

Posted - 2008-03-20 : 23:42:17

Annas,
Try this
Update OrderItem SET Name = 'Pie', Price = ' 2'
having rowid = (select max(rowid) from OrderItem where orderid = 1)

johnsql
Posting Yak Master

161 Posts

Posted - 2008-03-21 : 11:41:12

quote:
Originally posted by mayoorsubbu

Annas,
Try this
Update OrderItem SET Name = 'Pie', Price = ' 2'
having rowid = (select max(rowid) from OrderItem where orderid = 1)

The structure of OP's OrderItem table does not have a defined column 'rowid', so I am afraid that the solution won't work to update the last inserted row. Maybe the table must be altered before the solution is applied. I do not know whether there is another way to solve the problem.

mayoorsubbu
Yak Posting Veteran

95 Posts

Posted - 2008-03-22 : 01:55:20

Johnsql,
You are right. There is no psuedo col rowid in Sql Server unlike Oracle, hence OP may have to create one or a dateinserted col. There is no other way to solve this problem.

PABluesMan
Starting Member

26 Posts

Posted - 2008-03-26 : 10:55:40

It's very simple: add an identity column. Then your query can look like this:

/**********************************************************************************************/
DECLARE @Ident INT

INSERT INTO OrderItem(OrderID)
VALUES(1)

SET @Ident = IDENT_CURRENT ('OrderItem')

UPDATE OrderItem SET Name = 'Pie', Price = ' 2' WHERE <Identity field name> = @Ident
/**********************************************************************************************/

Hope this helps!

I geek, therefore I am

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources