Sql Query - 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

Development Tools

ASP.NET

Sql Query

Author

Topic

vk18
Posting Yak Master

146 Posts

Posted - 2007-04-25 : 14:35:46

Hello Guys,

I have a below query that i am trying to update one row. This below query is updating all the rows of invoicetotal. I want to update
only the max(tblinvoice.invoicenumber). How can i include in this query. Any Ideas..?
Thx

update tblinvoice set invoicetotal = (SELECT SUM(rateActual) AS Rateactuals FROM tblSpot inner join tblinvoice ON tblSpot.fkInvoice = tblInvoice.pkid WHERE tblspot.fkcontracttype = 'UNWIRED'
and tblinvoice.fkgroup = '2235' and tblinvoice.fksubgroup = '33' and
tblspot.dateactual between '2/26/2007' and '3/25/2007')

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-04-25 : 14:38:28

because your query is incorrect. The way you have it currently is


UPDATE tblInvoice SET InvoiceTotal = ()

To update specific rows you need a where condition


UPDATE tblInvoice SET InvoiceTotal = ( SELECT.... WHERE ...) 
WHERE <Condition>

************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

vk18
Posting Yak Master

146 Posts

Posted - 2007-04-25 : 14:43:25

quote:
Originally posted by dinakar

because your query is incorrect. The way you have it currently is

UPDATE tblInvoice SET InvoiceTotal = ()

To update specific rows you need a where condition

UPDATE tblInvoice SET InvoiceTotal = ( SELECT.... WHERE ...)
WHERE <Condition>

************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Can i use 2 where conditions in my Query.? I am already using one
THx

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-04-25 : 14:44:40

sure..


UPDATE tblInvoice SET InvoiceTotal = ( SELECT.... WHERE ...) 
WHERE <Condition1>  
   AND/OR <condition2>
   AND/OR <condition3>...

************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

vk18
Posting Yak Master

146 Posts

Posted - 2007-04-25 : 14:53:50

quote:
Originally posted by dinakar

sure..

UPDATE tblInvoice SET InvoiceTotal = ( SELECT.... WHERE ...)
WHERE <Condition1>
AND/OR <condition2>
AND/OR <condition3>...

************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Hi Dinkar,

I tried this but this doesn't work. see below. Can you tell me what i am doing wrong.?
Thx

update tblinvoice set invoicetotal = (SELECT SUM(rateActual) AS Rateactuals FROM tblSpot inner join tblinvoice ON tblSpot.fkInvoice = tblInvoice.pkid WHERE tblspot.fkcontracttype = 'UNWIRED'
and tblinvoice.fkgroup = '2235' and tblinvoice.fksubgroup = '33' and
tblspot.dateactual between '2/26/2007' and '3/25/2007') where tblinvoice.invoicenumber = select max(invoicenumber)

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-04-25 : 15:11:21

[code]
where tblinvoice.invoicenumber = select max(invoicenumber)
[/code]

SELECT MAX()..from where? its an incomplete SELECT Statement.

************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

vk18
Posting Yak Master

146 Posts

Posted - 2007-04-25 : 15:16:23

quote:
Originally posted by dinakar

where tblinvoice.invoicenumber = select max(invoicenumber)

SELECT MAX()..from where? its an incomplete SELECT Statement.

************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

I tried this also,

Select max(invoicenumber) from tblinvoice but still doesn't work.
can i have 2 select classes in one query..?

vk18
Posting Yak Master

146 Posts

Posted - 2007-04-25 : 15:21:17

quote:
Originally posted by vk18

quote:
Originally posted by dinakar

where tblinvoice.invoicenumber = select max(invoicenumber)

SELECT MAX()..from where? its an incomplete SELECT Statement.

************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

I tried this also,

Select max(invoicenumber) from tblinvoice but still doesn't work.
can i have 2 select classes in one query..?

Hi,
I figurd out, i have to put that last query in Parenthesis. It works
Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources