October 26, 2020 at 12:47 am
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
October 26, 2020 at 1:43 pm
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/
October 26, 2020 at 1:53 pm
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
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
October 26, 2020 at 1:54 pm
Sounds like
Update tba
Set fta1 = 'ftbid' + b.col + CONVERT(VARCHAR(2), b.orderdate, 103)
Join tbb b on tba.fid = b.fid
October 26, 2020 at 1:57 pm
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.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
October 26, 2020 at 2:16 pm
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
October 26, 2020 at 2:27 pm
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
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
October 26, 2020 at 2:33 pm
October 26, 2020 at 3:38 pm
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.
October 26, 2020 at 4:49 pm
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
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
October 27, 2020 at 1:00 am
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!
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply