How to encode the following dynamical SQL scripts

  • I use one table (call first table) field's value to update another another table (call second table) field's value, but the field of the second table is not fixed,

    only the first three charaters we know beforehand (it is ftbid), but the other characters from the 2nd table, the other character is from CONVERT(VARCHAR(2), b.orderdate, 103).

    How to encode the following dynamic SQL scripts, thanks

    update tba set fta1='ftbid' +CONVERT(VARCHAR(2), b.orderdate, 103) from tba a, tbb b

    where a.fid=b.fid

  • If you provide DDL, samples data and expected results, we can help.  Not sure what you have and what you are looking for in your description

    For better, quicker answers, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • I don't see anything that would require this query to be written dynamically. This should work:

    UPDATE a
    SET fta1 = 'ftbid' + CAST(DAY(b.orderdate) AS VARCHAR(2))
    FROM tba a
    JOIN tbb b
    ON a.fid = b.fid;

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • Sounds like

    Update tba

    Set fta1 = 'ftbid' + b.col + CONVERT(VARCHAR(2), b.orderdate, 103)

    Join tbb b on tba.fid = b.fid

     

  • 892717952 wrote:

    I use one table (call first table) field's value to update another another table (call second table) field's value, but the field of the second table is not fixed,

    only the first three charaters we know beforehand (it is ftbid), but the other characters from the 2nd table, the other character is from CONVERT(VARCHAR(2), b.orderdate, 103).

    How to encode the following dynamic SQL scripts, thanks

    update tba set fta1='ftbid' +CONVERT(VARCHAR(2), b.orderdate, 103) from tba a, tbb b

    where a.fid=b.fid

    You might want to reformat your query as follows:

    UPDATE a
    SET fta1 = 'ftbid' + CONVERT(VARCHAR(2), b.orderdate, 103)
    FROM tba AS a
    INNER JOIN tbb AS b
    ON a.fid = b.fid;

    This way, the UPDATE clause references the table alias instead of the table name, which is the best way to write an update query when you are also using table aliases.   Also, rather than providing an implicit CROSS JOIN and then filtering the query, it's probably better to just use INNER JOIN.  I don't see any part of this query as being "dynamic", as dynamic SQL is defined as a character string that is then executed using the EXECUTE statement     Finally, by formatting the query into separate lines for the UPDATE, SET, FROM, JOIN, and WHERE clauses, the query is also much easier to read.

    • This reply was modified 4 years ago by  sgmunson. Reason: EDIT: added sentence about no dynamic SQL seen in the original post

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

  •  

    if I got it correctly the dynamic requirement is that its not the content of the field that is being updated but rather that the field it is being used for the update is unknown and varies.

    so if I got it correctly and using an updated version of the sql the following may work

    CONVERT(VARCHAR(2), b.orderdate, 103) - this returns the day part of the date - so values can be 01 to 31

    so a possible table b definition is as follows - note that if the columns do not always exist then this really needs to be dynamic sql - and not just 1 but multiples based on the contents of orderdate


    fid - key
    ftbid01
    ftbid02
    ftbid...
    ftbid31



    UPDATE a
    SET fta1 = case t.dayx
    when '01' then ftbid01
    when '02' then ftbid02
    when '03' then ftbid03
    ....
    when '31' then ftbid31
    end
    FROM tba AS a
    INNER JOIN tbb AS b
    ON a.fid = b.fid
    outer apply (select CONVERT(VARCHAR(2), b.orderdate, 103) as dayx
    ) t;

    EDIT: correct type on column names

  • Frederico .... maybe you're onto something.

    Your query has multiple references to ftbid01 which obviously should be to 01, 02, 03, ...

    The outer apply could be a cross apply, or do you disagree?

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • Mike01 wrote:

    If you provide DDL, samples data and expected results, we can help.  Not sure what you have and what you are looking for in your description

    Please kind see my explanation in the picture, thanks!

    Attachments:
    You must be logged in to view attached files.
  • Phil Parkin wrote:

    Frederico .... maybe you're onto something.

    Your query has multiple references to ftbid01 which obviously should be to 01, 02, 03, ...

    The outer apply could be a cross apply, or do you disagree?

    yeah.. copy and paste.

    In this case outer or cross is same thing - I do tend to use outer apply (left outer) instead of cross apply (inner join)

     

    to the OP - see the example I gave you - as I said if your tbb table contains all the possible 31 columns then the example I gave will work.

    if not then a bit of dynamic sql will be required to build the list of available columns before executing it - but no point in wasting time doing it here if not required.

     

  • In this case outer or cross is same thing - I do tend to use outer apply (left outer) instead of cross apply (inner join)

    I have no idea whether this is valid or not, but I use cross apply whenever possible, for the same reason the I use inner join whenever possible, in the vague hope that a cross apply is more specific and therefore gives the optimiser the best chance of coming up with a good plan.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • frederico_fonseca wrote:

    if I got it correctly the dynamic requirement is that its not the content of the field that is being updated but rather that the field it is being used for the update is unknown and varies.

    so if I got it correctly and using an updated version of the sql the following may work

    CONVERT(VARCHAR(2), b.orderdate, 103) - this returns the day part of the date - so values can be 01 to 31

    so a possible table b definition is as follows - note that if the columns do not always exist then this really needs to be dynamic sql - and not just 1 but multiples based on the contents of orderdate


    fid - key
    ftbid01
    ftbid02
    ftbid...
    ftbid31



    UPDATE a
    SET fta1 = case t.dayx
    when '01' then ftbid01
    when '02' then ftbid02
    when '03' then ftbid03
    ....
    when '31' then ftbid31
    end
    FROM tba AS a
    INNER JOIN tbb AS b
    ON a.fid = b.fid
    outer apply (select CONVERT(VARCHAR(2), b.orderdate, 103) as dayx
    ) t;

    EDIT: correct type on column names

    Thank you for you kind help!  Frederico

    the scripts you suggested can meet my request.

    As you known, which field of table b will be used to update the field of table a, it relys on the value of  CONVERT(VARCHAR(2), b.orderdate, 103) , I just want to know  how we can encode the script dynamically, thank you so much!

     

  • Phil Parkin wrote:

    Frederico .... maybe you're onto something.

    Your query has multiple references to ftbid01 which obviously should be to 01, 02, 03, ...

    The outer apply could be a cross apply, or do you disagree?

     

    Thank you for you kind suggestion !

Viewing 12 posts - 1 through 11 (of 11 total)

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