UPDATE SET Query with variable?

Question

Post reply

UPDATE SET Query with variable?

JPLeBlanc

Old Hand

Points: 365
More actions
December 17, 2008 at 12:36 pm

#204200

Hello everyone, I am new here so i hope im not stepping on any toes, i am looking to make an update query that i can run upon the import of a new set of data. from there i will join it to another table to build an organizational hierarchy. Unfortunately i dont have key that will work with both tables due to there being historical data to take into account.
the simple part of this question would be an update statement that i would like to write that would add a column with a variable date depending on when i upload the data.
update dbo.[PS - daily Temp]
set [report Date] = @report as smalldatetime
Where Report Date is null
so when i run this of course errors out, could someone assist me with the correct syntax to be able to add so the statement will prompt me with a dialog box to input the column data which is a date?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Nicholas Cain SSC-Dedicated Points: 33009 More actions · Answer 1

Not this?

declare @report smalldatetime

select @report = getdate()

update dbo.[PS - daily Temp]

set [report Date] = @report

Where [Report Date] is null

Shamless self promotion - read my blog http://sirsql.net

JPLeBlanc Old Hand Points: 365 More actions · Answer 2

Almost perfect! thanks! except that will provide the time right now. I was trying to avoid this as i am always working with the prior day, and sometimes i will not be able to update the data daily immeditately, so i may get anywhere up to a week behind on the import.

What i was really looking for was to be prompted to enter the date prior to writing the data to the table.

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 3

SSMS will not prompt you for a parameter like that. You need to modify this yourself every time you run the script.

You can use the DATEADD function to get a date from yesterday, as in:

DATEADD(day, -1, getdate()) -- yesterday at this time

Now, if you don't want to include the time:

DATEADD(day, DATEDIFF(day, 0, getdate()) - 1, 0) -- yesterday's date, 00:00:00.000 time

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

JPLeBlanc Old Hand Points: 365 More actions · Answer 4

Ughhh... I was afraid that would be the answer...

Thanks for the assistance! 🙂 it is much appreciated.

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 5

DOS batch job with a VB Script to popup an input window. Either that, or a "control" table that will remember the last run date.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)