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
You might want to reformat your query as follows:
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.