Post reply

how to populate a variable with a query result

  • February 20, 2003 at 3:19 pm

    #79539

    i have a variable to which i want to assign the value from a specific field in the 6th row of a recordset. nothing is working. help?

  • February 20, 2003 at 3:37 pm

    #449429

    Can you explain in more detail your code. Are we talking SQL variable or ASP variable and how many items. An example would help.

  • February 20, 2003 at 3:43 pm

    #449430

    my code begins like this:

    "

    select top 6 * from tblMachineInfo

    order by ID_Machine_Info desc

    declare @@ID_Machine_Info char(4)

    "

    the result will be the records with the 6 highest values for the field ID_Machine_Info, a 4-digit numberic value. I want to delete the records with the 5 highest values, by assigning the 6th value to the variable and then taking everything greater than that for the delete statement.

    thanks

  • February 20, 2003 at 4:37 pm

    #449433

    Would this answer your request?

    DELETE tblMachineInfo

    WHERE ID_Machine_Info IN

    ( SELECT TOP 5 ID_Machine_Info FROM tblMachineInfo ORDER BY ID_Machine_Info DESC )

  • February 20, 2003 at 5:00 pm

    #449440

    yes, almost completely, thanks; but i still would like to know how to assign the previously-described value to the variable, for various other reasons. can that be done?

    also, in the code you posted, i tried to add a "where Date = GetDate() " clause right before the "ORDER BY" statement; to ensure that only the current day's data can be deleted in this manner. The result returned 0 rows, even though there are 5 rows that meet the criteria. Any advice?

  • February 21, 2003 at 6:50 am

    #449471

    To get the MachineIDs into a variable, do a SELECT INTO a #temp table before doing the update.

    As for the GETDATE() part of your question, ensure that you strip the time information from the GETDATE() return so as to only compare the date part of the value:

    WHERE Convert(CHAR(10), DateField) = Convert(CHAR(10), GETDATE())

  • February 24, 2003 at 1:06 am

    #449537

    Also consider using the DateDiff function.

    E.g. WHERE DATEDIFF(d, date, GetDate()) = 0

  • February 24, 2003 at 11:21 am

    #449608

    How about this (assuming your ID field is an identity column) --

    DECLARE @ID_Machine_Info int

    SELECT TOP 1 @ID_Machine_Info = MI.ID_Machine_Info FROM

    (SELECT TOP 6 ID_Machine_Info FROM tblMachineInfo

    WHERE Date >= CONVERT(CHAR(10), GETDATE(), 102)

    ORDER BY ID_Machine_Info DESC) MI

    ORDER BY MI.ID_Machine_Info

    IF NOT @ID_Machine_Info IS NULL

    DELETE tblMachineInfo

    WHERE ID_Machine_Info > @ID_Machine_Info

Viewing 8 posts - 1 through 7 (of 7 total)

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