Update Database - 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 2005 Forums

Transact-SQL (2005)

Update Database

Author

Topic

annas
Starting Member

36 Posts

Posted - 2007-10-08 : 03:46:36

Haii..
Can anyone help me in perfecting this code:


UPDATE Orders SET Name = Foods.Name, Price = Foods.Price, Code = Foods.Code FROM Foods WHERE foods.Code = 1 HAVING CustID=(Max(CustID))

I want to update Orders row where the food code is no 1 and the CustID is the max no..

Thank U

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-10-08 : 03:48:22

CustID is of which table ?

KH
[spoiler]Time is always against us[/spoiler]

annas
Starting Member

36 Posts

Posted - 2007-10-08 : 04:02:32

The same tables, the orders. The orders table have this attribute: OrderID, CustID, Name, Code and Price.

At first the OrderID and CustID are auto Increment. So the Name,code and price are set to null. So here i want to update the null column.

annas
Starting Member

36 Posts

Posted - 2007-10-08 : 04:25:10

I want to update only the Max CustID not all of it. Im trying this code also but it will affect all rows


SELECT CustID FROM Customer WHERE CustID=(SELECT Max(CustID) FROM Customer) 
UPDATE Orders SET Name = Foods.Name, Price = Foods.Price, Code = Foods.Code FROM Foods WHERE foods.Code = 1

Kristen
Test

22859 Posts

Posted - 2007-10-08 : 04:39:58

This perhaps?


UPDATE U
SET Name = Foods.Name ...
FROM Orders AS U
JOIN Foods AS F
     ON F.XXX = U.XXX
    AND F.Code = 1
WHERE U.CustID=(SELECT Max(CustID) FROM Customer)

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources