Post reply

T-SQL Update question

  • December 20, 2001 at 12:37 am

    #78366

    Hi:

    I'm new to SQL/T-SQL. Migrating an Access application. I've scanned/read a few books but haven't found the information I'm looking for.

    I have a simple task to pre-process the contents of a table. It looks at ALL records in the table (usually 50 or so records, 200 would probably be a high water mark). The table comes from an outside source and is imported into the database prior to the pre-processing.

    Specifically, one part of that task is to update a field (RELATION) based on the value of two other fields (EVALUTION and CHANNEL)

    Using ACCESS, the SQL statement used is:

    "UPDATE TempNew SET RELATION = iif(EVALUATION=TRUE,'EVALUATION',iif(trim(Channel)='DIRECT','CUSTOMER',iif(trim(Channel)='DISTRIBUTOR','DISTRIBUTOR','UNKNOWN')))"

    which translates to:

    If EVALUATION = TRUE

    RELATION = 'EVALUATION'

    If EVALUATION = FALSE then

    If CHANNEL = 'DIRECT'

    RELATION = 'CUSTOMER

    Else

    If CHANNEL = 'DISTRIBUTOR'

    RELATION = 'DISTRIBUTOR'

    Else

    RELATION = 'UNKNOWN'

    I can come up with a couple of ways to do this in T-SQL, but I'm trying to establish the 'right' way to do it.

    For instance, I've tested the following as a stored procedure:

    Alter Procedure StoredProcedure1

    As

    UPDATE TempNew

    SET Relation 'EVALUATION'

    WHERE Evaluation = 1

    UPDATE TempNew

    SET Relation = 'CUSTOMER'

    WHERE Evaluation = 0 AND CHANNEL='DIRECT'

    UPDATE TempNew

    SET Relation = CHANNEL

    WHERE Evaluation = 0 AND

    CHANNEL='DISTRIBUTOR'

    UPDATE TempNew

    ISNULL(Relation,'UNKNOWN')

    return

    But, while it works, it seems terribly inefficient. That appears to be four separate queries and therefore four passes through the data. However, no row is returned or updated more than once. But maybe it's not four separate queries/passes. Maybe SQL Server gets the entire statement, evaluates it and processes it in a single pass. Or maybe four separate queries/passes where there's no overlap IS efficient.

    I've read that SQL is oriented toward 'set processing' vs 'row processing', but I haven't found an explanation of what the goal of a 'set processing' approach is when writing a SQL statement.

    I'm not sure what qualifies as 'efficient' for set processing, what my goal is when attempting to write a SQL UPDATE statement.

    Any examples I've seen have been very simplistic and not much help.

    If anyone can recommend a good book on the subject, or some other reference, I'd be more than happy to look into that.

    I understand that a cursor can be used for 'row processing', but this doesn't seem like a 'row processing' issue. Besides, I can use ADO to handle row processing on a single result set.

    Thanks for whatever help you can provide.

    JK

  • December 20, 2001 at 1:13 am

    #421323

    that WILL be treated as four different queries ...

    this should do it in one

    UPDATE TempNew

    SET Relation = CASE Evaluation

    WHEN 1 THEN 'EVALUATION'

    WHEN 0 THEN

    CASE CHANNEL WHEN 'DIRECT' THEN 'CUSTOMER'

    WHEN 'DISTRIBUTOR' THEN 'DISTRIBUTOR'

    ELSE 'UNKNOWN'

    END

    END

    set based processing would mean that you would leave all the recursions/iterations for the SQL Query engine .. let it do all the work .. in one word try and avoid cursors

  • December 20, 2001 at 1:41 am

    #421326

    Well, that makes perfect sense! Thank you. I don't know why I missed the application of CASE here. There's a very different approach when using T-SQL than what I've been used to.

    Again, thanks.

    JK

Viewing 3 posts - 1 through 2 (of 2 total)

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