Using MAX in Update statement *** RESOLVED ***

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

Transact-SQL (2000)

Using MAX in Update statement *** RESOLVED ***

Author

Topic

sherrireid
Yak Posting Veteran

58 Posts

Posted - 2009-11-23 : 15:45:10

Hi all:

I am trying to get the max payment date and then based on that, if the vendor has a max payment date that is less than 06/30/2006 I want to update the vendor to be inactive.

Forum member KHTAN helped me write a query to find these folks, but now I am having problems turning this query into an update statement. This is the first time I've used MAX in an update statement - I've tried a ton of things and am at my wits end.

The following code runs without errors, but does not update any records and I don't understand why. I've set the code to be equal to 06/30/2006 right now during testing. There are 3 records with this date, and they should have been inactivated - and they weren't.

update VEND_REMIT
set VEND_REMIT.INACT_IND = 'Y'

from
(
	select 
		MAX(PFM_TEST.dbo.PYMT.DOC_DATE) AS 'LastPaymentDate'
		
	from 	
		PMM_TEST.dbo.VEND_REMIT 
		join PMM_TEST.dbo.VEND_TERMS 
		on   PMM_TEST.dbo.VEND_REMIT.VEND_TERMS_ID    = PMM_TEST.dbo.VEND_TERMS.VEND_TERMS_ID
		join PFM_TEST.dbo.PYMT
		on   PFM_TEST.dbo.PYMT.VEND_CODE              = PMM_TEST.dbo.VEND_REMIT.VEND_NO
	group by 
		PMM_TEST.dbo.VEND_REMIT.NAME, 
		PMM_TEST.dbo.VEND_REMIT.VEND_NO
) AS PleaseWork
where	
	'LastPaymentDate' = '20060630'

Any help you can provide would be greatly appreciated.

Thanks!
Sherri Reid

SLReid
Forum Newbie
Renton, WA USA

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-11-25 : 02:07:41

Try this

update VEND_REMIT
set VEND_REMIT.INACT_IND = 'Y'
where	
	LastPaymentDate = 
(	select 
		MAX(PFM_TEST.dbo.PYMT.DOC_DATE) AS 'LastPaymentDate'
		
	from 	
		PMM_TEST.dbo.VEND_REMIT 
		join PMM_TEST.dbo.VEND_TERMS 
		on   PMM_TEST.dbo.VEND_REMIT.VEND_TERMS_ID    = PMM_TEST.dbo.VEND_TERMS.VEND_TERMS_ID
		join PFM_TEST.dbo.PYMT
		on   PFM_TEST.dbo.PYMT.VEND_CODE              = PMM_TEST.dbo.VEND_REMIT.VEND_NO
	group by 
		PMM_TEST.dbo.VEND_REMIT.NAME, 
		PMM_TEST.dbo.VEND_REMIT.VEND_NO
	WHERE LastPaymentDate = '20060630'
)

Madhivanan

Failing to plan is Planning to fail

sherrireid
Yak Posting Veteran

58 Posts

Posted - 2009-12-01 : 15:43:37

I've been away on vacation - I was very happy to see someone had tried to help me - I really appreciate it. I'm very sad to say that this throws up an error though: 'Msg 156, Level 15, State 1, Line 17 Incorrect syntax near the keyword 'WHERE'.' Do you have any ideas? Thanks.

SLReid
Forum Newbie
Renton, WA USA

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-12-01 : 16:04:14

Take "WHERE LastPaymentDate = '20060630'" before the group by clause.

No, you're never too old to Yak'n'Roll if you're too young to die.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-12-02 : 01:21:52

quote:
Originally posted by webfred

Take "WHERE LastPaymentDate = '20060630'" before the group by clause.

No, you're never too old to Yak'n'Roll if you're too young to die.

Yes it is. I diddn't notice it

Madhivanan

Failing to plan is Planning to fail

sherrireid
Yak Posting Veteran

58 Posts

Posted - 2009-12-04 : 16:48:11

quote:
Originally posted by madhivanan

quote:
Originally posted by webfred

Take "WHERE LastPaymentDate = '20060630'" before the group by clause.

No, you're never too old to Yak'n'Roll if you're too young to die.

Yes it is. I diddn't notice it

Madhivanan

Failing to plan is Planning to fail

Duh. I didn't notice it either. The only problem is that when I put it in the right place, I am still having problems. If I use the following code I get the errors that I will list below the code:

update VEND_REMIT
set VEND_REMIT.INACT_IND = 'Y'
where	
	LastPaymentDate = 
(	select 
		MAX(PFM_TEST.dbo.PYMT.DOC_DATE) AS 'LastPaymentDate'
		
	from 	
		PMM_TEST.dbo.VEND_REMIT 
		join PMM_TEST.dbo.VEND_TERMS 
		on   PMM_TEST.dbo.VEND_REMIT.VEND_TERMS_ID    = PMM_TEST.dbo.VEND_TERMS.VEND_TERMS_ID
		join PFM_TEST.dbo.PYMT
		on   PFM_TEST.dbo.PYMT.VEND_CODE              = PMM_TEST.dbo.VEND_REMIT.VEND_NO

WHERE LastPaymentDate = '20060630'	
	
	group by 
		PMM_TEST.dbo.VEND_REMIT.NAME, 
		PMM_TEST.dbo.VEND_REMIT.VEND_NO

Msg 207, Level 16, State 3, Line 2
Invalid column name 'LastPaymentDate'.
Msg 207, Level 16, State 3, Line 2
Invalid column name 'LastPaymentDate'.

So OK - the ONLY 'LastPaymentDate' that actually has quotes around it is the select MAX(PFM_TEST.dbo.PYMT.DOC_DATE) AS 'LastPaymentDate' statement. So I thought I'd put single quotes around the other two LastPaymentDate's that appear in the code. Like so:

update VEND_REMIT
set VEND_REMIT.INACT_IND = 'Y'
where	
	'LastPaymentDate' = 
(	select 
		MAX(PFM_TEST.dbo.PYMT.DOC_DATE) AS 'LastPaymentDate'
		
	from 	
		PMM_TEST.dbo.VEND_REMIT 
		join PMM_TEST.dbo.VEND_TERMS 
		on   PMM_TEST.dbo.VEND_REMIT.VEND_TERMS_ID    = PMM_TEST.dbo.VEND_TERMS.VEND_TERMS_ID
		join PFM_TEST.dbo.PYMT
		on   PFM_TEST.dbo.PYMT.VEND_CODE              = PMM_TEST.dbo.VEND_REMIT.VEND_NO

WHERE 'LastPaymentDate' = '20060630'	
	
	group by 
		PMM_TEST.dbo.VEND_REMIT.NAME, 
		PMM_TEST.dbo.VEND_REMIT.VEND_NO

But when I do that - I get this error:

Msg 295, Level 16, State 3, Line 2
Syntax error converting character string to smalldatetime data type.

I don't know if I am on the right track by changing the LastPaymentDates to have a single quote around them or not. And if I am, I am sort of at a loss as to which LastPaymentDate it is yelling about (the last one?) and how to fix it.

I'm sorry - I'm really not an idiot - this is just new territory for me.

SLReid
Forum Newbie
Renton, WA USA

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-12-05 : 11:00:06

First you shouldn't need to put any quotes around column names!
When error message says: Invalid column name 'LastPaymentDate' then the problem is that there is no column with that name in table.

To get more and better help it is about time to give the relevant table structure and sample data...

No, you're never too old to Yak'n'Roll if you're too young to die.

sherrireid
Yak Posting Veteran

58 Posts

Posted - 2009-12-05 : 15:08:02

quote:
Originally posted by webfred

First you shouldn't need to put any quotes around column names!
When error message says: Invalid column name 'LastPaymentDate' then the problem is that there is no column with that name in table.

To get more and better help it is about time to give the relevant table structure and sample data...

No, you're never too old to Yak'n'Roll if you're too young to die.

WebFred,

Yes, I do know that you shouldn't need to put quotes around column names and that if it is yelling about it - then the problem is that there is no column with that name. Under most circumstances, I would have gone that route. Because I used select MAX(PFM_TEST.dbo.PYMT.DOC_DATE) AS 'LastPaymentDate' and it had quotes around it and the other two LastPaymentDates in the code DIDN'T - that is why I thought I should at least TRY to see if that resolved the problem. Faulty thinking maybe...but I was trying.

As for Table Structure and sample data... My apologies for not providing this information earlier in the process:

PMM_TEST.dbo.VEND_REMIT
-----------------------
INACT_IND char(1)
NAME char(40)
VEND_NO char(20)

PFM_TEST.dbo.PYMT
-----------------
Unfortunately, these are vendor databases - and the PYMT table is hidden in the Object Explorer so I can't give you specifics on the PYMT table. I can tell you this:

PYMT.DOC_DATE is definitely a datetime field. If I query it using '20060630', 2006/06/30 or '06/30/2006' I get the same results. And this is a 2000 database - so none of the 2008 date/time field type changes have been introduced yet.

Since VEND_NO is in the other table, I have to assume that it has the same properties in this table: char(20)

Here is some example data:


RemitVendorName           VEND_NO Inactive Last Payment Date
ESI LEDERLE INC.	          ESIL001  N	   2006-06-30 00:00:00
PETER PEPPER PRODUCTS INC PETE007  N	   2006-06-30 00:00:00
COAXIA INC	          COAX001  N	   2006-06-30 00:00:00

During this process I noticed that I hadn't removed an unnecessary table from the join - VEND_TERMS. It wasn't doing any harm - but it doesn't need to be there. So I've cleaned up the code that I am trying to work with and am re-posting here:


update 
PMM_TEST.dbo.VEND_REMIT 
set PMM_TEST.dbo.VEND_REMIT.INACT_IND = 'Y'
where LastPaymentDate = 

(select 	MAX(PFM_TEST.dbo.PYMT.DOC_DATE) AS 'LastPaymentDate'
		
	from 	
		PMM_TEST.dbo.VEND_REMIT 
		join PFM_TEST.dbo.PYMT
		on   PFM_TEST.dbo.PYMT.VEND_CODE  = PMM_TEST.dbo.VEND_REMIT.VEND_NO

WHERE LastPaymentDate = '20060630'	
	
	group by 
		PMM_TEST.dbo.VEND_REMIT.NAME, 
		PMM_TEST.dbo.VEND_REMIT.VEND_NO )

Thanks for any help you or anyone else may be able to provide.

Regards,
Sherri

SLReid
Forum Newbie
Renton, WA USA

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-12-05 : 16:15:45

I am sorry for not understanding so quick what you want.
Can you please complete this:
I want to set the INACT_IND to 'Y' if the following conditions are true:
...

No, you're never too old to Yak'n'Roll if you're too young to die.

sherrireid
Yak Posting Veteran

58 Posts

Posted - 2009-12-07 : 14:09:43

I want to set the INACT_IND to 'Y' when max(PFM_TEST.dbo.PYMT.DOC_DATE) <= 06/30/06. (I had it set to = in the code only because I was testing.)

Vendors get many payments so there are hundreds of payment records. First I need to find the last time each vendor was paid. If the last time the vendor was paid, was > 06/30/06, I want to ignore them.

Once I have determined which ones to inactivate - then I want to set those vendors so their INACT_IND flag = 'Y'.

Thank you very much for continuing to help me.

Regards,
Sherri Reid

SLReid
Forum Newbie
Renton, WA USA

sherrireid
Yak Posting Veteran

58 Posts

Posted - 2009-12-14 : 14:01:34

Is there anyone who can help me with this? I am really in a bind.

Thanks in advance.

Sherri

SLReid
Forum Newbie
Renton, WA USA

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-12-14 : 14:16:41

Can you try this once..

update a 
set a.INACT_IND = 'Y'
from PMM_TEST.dbo.VEND_REMIT a 
inner join 
(select PMM_TEST.dbo.VEND_REMIT.NAME as [NAME],PMM_TEST.dbo.VEND_REMIT.VEND_NO as [VEND_NO],MAX(PFM_TEST.dbo.PYMT.DOC_DATE) AS [LastPaymentDate]	
	from PMM_TEST.dbo.VEND_REMIT 
	inner join PFM_TEST.dbo.PYMT	on   PFM_TEST.dbo.PYMT.VEND_CODE  = PMM_TEST.dbo.VEND_REMIT.VEND_NO
	group by PMM_TEST.dbo.VEND_REMIT.NAME, 	PMM_TEST.dbo.VEND_REMIT.VEND_NO ) b
on a.NAME = b.NAME and a.VEND_NO = b.VEND_NO
where b.[LastPaymentDate] <= '2006-06-30'

sherrireid
Yak Posting Veteran

58 Posts

Posted - 2009-12-16 : 14:32:24

vijayisonly !!!!! You are the best! That worked exactly how I needed it to. Thank you so much for your help!

Sherri Reid

SLReid
Forum Newbie
Renton, WA USA

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-12-16 : 14:37:37

quote:
Originally posted by sherrireid

vijayisonly !!!!! You are the best! That worked exactly how I needed it to. Thank you so much for your help!

Sherri Reid

SLReid
Forum Newbie
Renton, WA USA

Np..You're welcome. Just glad to help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources