[RESOLVED] Update record on SQL

[mlosso]

I am using MS SQL SERVER 2000 with a PERL SCRIPT.

UPDATE [CustSat].Customers_T
	SET [CustSat].[Customers_T].LastEmailSent = GETDATE(),[CustSat].[Customers_T].LastTicketSent = TicketAssignment_T.TicketNumber
	SELECT TOP $survey_total TicketAssignment_T.Customer_ID, TicketNumber, LanID, EmailAddress, ShortDescription
	FROM TicketAssignment_T
	INNER JOIN Customers_T
	ON (TicketAssignment_T.Customer_ID = Customers_T.Customer_ID)
	WHERE((DATEDIFF(day,Customers_T.LastEmailSent, GETDATE()) > $customer_days) OR (Customers_T.LastTicketSent IS NULL))
	ORDER BY NEWID()

Where ever you see a $ is a variable from a perl script.


Anyways, what the code is supposed to do is just update the TOP #, which are selected randomly by used ORDER BY NEWID(), and update those values with the current date. Instead, it is updating every row in the table with the current date. How could I change this?

[mlosso]

I really need help on this? Can you do UPDATES on INNER JOINS?

[szlamany]

UPDATE/FROM is the syntax you are looking for...

UPDATE Customers_T
	SET	 LastEmailSent = GETDATE()
                          ,LastTicketSent = T1.TicketNumber

	From Customers_T C1
	Left Join TicketAssignment_T T1 ON (T1.Customer_ID
						= C1.Customer_ID)
	WHERE((DATEDIFF(day,C1.LastEmailSent, GETDATE()) > (...SUBQUERY GOES HERE...)
		OR (C1.LastTicketSent IS NULL))

Something like that should work. The UPDATE/SET should not have aliases (like the C1) - they are part of the FROM/JOIN select. The RIGHT-SIDE of the = sign in the SET's can have ALIAS's.

[mlosso]

You are awesome, only a few things:

1. After the WHERE Clause, it will not allow me to do: ORDER BY NEWID(). That is really important, becuase this needs to be a random selection

2. At this line of code:

SET	 LastEmailSent = GETDATE()
                          ,LastTicketSent = T1.TicketNumber

It said T1 is not apart of the Customer_T table, therefore I cant reference it.

Also the DATEDIFF comparison is to a variable value that I send in.

[szlamany]

I don't understand the need for a random update - you cannot put an ORDER BY on an UPDATE - so please clarify your reason.

The T1 error makes no sense - we are joining the "T1" alias table to our "C1" table - that makes those aliases available on the right-side of the equal sign.

[mlosso]

The reason for the random update is because, I am selecting a specified amount of rows to send survey's too. Those who are sent surveys are supposed to be chosen at random, to the amount specified as long as the fit the date criteria. It really isnt a random update, more of a random selection and then out of those randomly selected, they are updated with the new date.

[szlamany]

Well - since this is MS SQL 2000 I would make a STORED PROCEDURE to do this job...

Seems like you would need to create either a TABLE VARIABLE (if not too many rows) or a TEMP TABLE with the "selected" records to choose. Just the primary keys is enough...

Then the SPROC would update from that "table" of primary keys...

Or on second thought maybe you could:

WHERE PRIKEY IN (SELECT TOP 100 PRIKEY FROM ... ORDER BY NEWID())

That might work...

[mlosso]

I wanted to do a stored procedure, but cannot because I am using PERL to send out these emails.

[szlamany]

I wanted to do a stored procedure, but cannot because I am using PERL to send out these emails.

I have never used PERL...

Maybe my "second thought" suggestion will work...

[mlosso]

Trying it now...I dont really understand that that is doing though..

[mlosso]

Ok I tried this:

UPDATE Customers_T
	SET	 LastEmailSent = GETDATE()

	From Customers_T 
	Left Join TicketAssignment_T
		ON (TicketAssignment_T.Customer_ID = Customers_T.Customer_ID)
		WHERE Customers_T.Customer_ID IN 
			(SELECT TOP 40 TicketAssignment_T.Customer_ID, TicketNumber, LanID, EmailAddress, ShortDescription
					FROM TicketAssignment_T
					INNER JOIN Customers_T
					ON (TicketAssignment_T.Customer_ID = Customers_T.Customer_ID)
					WHERE DATEDIFF(day,Customers_T.LastEmailSent, GETDATE()) > 115
					ORDER BY NEWID()
			 )

I get the following error from query analyzer:

Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

[mlosso]

Ok, after I removed everything in the select list except for the first thing, it now works the way I want it too, except I need to figure out how I can get those other column headings

[mlosso]

Soooo close

[szlamany]

The "WHERE...IN..." only wants one single column - it's a silly trick to populate a list of id's to select from.

But this is important:

UPDATE Customers_T
	SET	 LastEmailSent = GETDATE()

	From Customers_T C1
	Left Join TicketAssignment_T T1

Those ALIAS assignments allow you to reference the fields from the C1 or T1 tables - realize that there are really three tables being hit in this type of UPDATE - the CUSTOMERS_T (without alias) is the UPDATE table - that's the ANSI standard syntax allowed for UPDATE. Having an UPDATE with a FROM clause like this brings in two other tables - CUSTOMERS_T again (with ALIAS) and TICKETASSIGNMENT_T (with ALIAS). Those C1.ColumnName or T1.ColumnName values should be available on the right-side of the = sign in the SET part of the UPDATE.

It's bothering me a little that you decided to go with ALIAS in the sub-query. SQL can sometimes pull the wrong table (you have like 5 tables in this query now) when you do not use an ALIAS with ambiguous references like this. You should have C2 and T2 aliases in that sub-query...

[mlosso]

Ok, I did the recommended changes for aliases, but I really need to figure out how get those other colums to return the row columns that I need.

[szlamany]

Are you saying it won't let you add this line????

,LastTicketSent = T1.TicketNumber

[mlosso]

No,

on the last select, i need to retrieve these column headings:

SELECT TOP 40 TicketAssignment_T.Customer_ID, TicketNumber, LanID, EmailAddress, ShortDescription

But I cannot because of the WHERE IN clause before. How can I get around this?

[szlamany]

Why do you need those other columns?

Are they all part of the "selection" of the record to be updated?

[mlosso]

Yes, this data will be used when I send out the emails. They must be retrieved.

[szlamany]

Yes, this data will be used when I send out the emails. They must be retrieved.

I'm not seeing how that makes any sense...

This is an UPDATE statement - it's not returning a recordset - right?

[mlosso]

I am trying to do both in one statment. I figured this had to be done because of the random selection, ie had to update while I was selecting...

[mlosso]

The idea is basically, while I am randomly selecting the rows based on the criteria, I am also updating their timestamp...

[szlamany]

I'm thinking that's not possible - unless someone else has an idea...

I know that MS SQL 2005 allows a SELECT output from an UPDATE - that's in the sticky at the top of the DB section of this forum - but that's new syntax...

All your updated rows will have the same GETDATE() in that spot - can't you use that for selection?

I don't know PERL and how it works - so I'm not going to be of much more help...

[mlosso]

All your updated rows will have the same GETDATE() in that spot - can't you use that for selection?

Perl is just the language I am using the execute these statments..nothing more. But you are a genius, thats what I need to do.... I think I tried it before, I just didnt do it right. I wish I could execute a stored prodcedure in perl, cause I would just save that GETDATE and use a selection. Thanks, I think I will try to go about it that way.

[szlamany]

You should be able to:

SELECT * FROM ... WHERE LASTEMAILSENT
                = (SELECT MAX(LASTEMAILSENT) FROM ...)

Good luck!

[mlosso]

Marking this as resolved for now.