Help with T-SQL and jobs 1

[dgillz]

Using the SQL server agent jobs, I am try to update a record in a table. The record in question has todays date as a key field. I am trying to update the field called "AP".

My SQL code is as follows:

UPDATE dbo.Execsum set AP=SELECT sum(amt_1 + amt_2) from APOPNFIL_SQL

I need help with the WHERE clause (today's date) and I am getting a syntax error near the SELECT statement.

Can anyone help with this?

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com

 

[SQLBill]

The SELECT statement needs to be in parenthesis.

You don't give enough information about how you need to use the WHERE, so....

Assuming that you have a column in EXECSUM table that has a datetime data type field AND that you only want to update the row(s) in AP that also have a datetime of today, try this: (GETDATE() returns today's date)

UPDATE dbo.Execsum
set AP=(SELECT sum(amt_1 + amt_2) from APOPNFIL_SQL)
WHERE datefield = GETDATE()

-SQLBill

 

[dgillz]

SQLBill,

Many thanks for the help with the SELECT statement. However this is still not working. Getdate() is returning a datetime, and the existing record I am trying to update also has a datetime with a value of a few seconds ago (created by an INSERT INTO statement of the first step of my job). As a result, the WHERE clause is not finding a matching record to update.

Any ideas on how to solve this?

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com

 

[vganeshbabu]

try this out

UPDATE dbo.Execsum
set AP=(SELECT sum(amt_1 + amt_2) from APOPNFIL_SQL)
WHERE convert(char,datefield,107) = convert(char,GETDATE(),107)

 

[dgillz]

Can you explain the significance of the 107 in the CONVERT statement?

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com

 

[vganeshbabu]

check this link..

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ca-co_2f3o.asp

it returns the output of date in a specified format for 107 it is Mon dd,yy

 

[dgillz]

Many thanks, vganeshbabu. I am new at SQL but learning every day.

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com

 

[vganeshbabu]

enjoy your learning.. for self solving these issues u can always search things in

http://www.google.com

 

[SQLBill]

You can also check out the Books OnLine (BOL) that comes with SQL Server. It should have installed when you installed SQL Server (look at START>PROGRAMS>Microsoft SQL Server> Books OnLine.

Depending on how your data is and what you are really trying to do, you may have to 'tweak' vganeshbabu's script. As it works now, you will be changing EVERY row that has today's date. If that's what you are trying to do, great, the script will work. However, if as I understood, you are trying to update a row that was recent (within a few minutes) you will need to make a change to the script.

Here's how to check for a time frame in your query:

UPDATE dbo.Execsum
set AP=(SELECT sum(amt_1 + amt_2) from APOPNFIL_SQL)
WHERE datefield >= convert(varchar(10),GETDATE(),120) + ' 05:00:00'

This takes todays date (2003-07-08 12:00:00 (EST) and changes it to 2003-07-08 and then adds the time 05:00:00 to it (2003-07-08 05:00:00) and compares that to your datefield. So, if your datefield is greater than or equal to that date and time, the update will take place.

Refer to the Books OnLine, use the Index tab and enter CONVERT, then select the Transact SQL reference for more information.

-SQLBill