pjerling
asked on
Building the column to be updated into the SQL update statement
Hi there,
I was wondering whether it is possible to update data via SQL by building the column you wish to update in the same query.
For example, I have a spreadsheet that needs imported/updated on a daily basis. The spreadsheet contains one value, e.g. Sales This Month. The database have 12 columns, M1Val, M2Val, .., M12Val. Where the value indicate the month no, e.g. 12 = Dec in this instance.
UPDATE DBA.SALES
SET 'M'+CAST( MONTH(CURRENT DATE) AS VARCHAR(2))+'VAL' = TMP.SALES_THIS_MTD
FROM DBA.SALES LEFT OUTER JOIN DBA.TMP_SALES TMP ON SALES.ACCT_NUMBER = TMP.SOURCE_ID
;
COMMIT
;
Current Month = 11
Therefore "'M'+CAST( MONTH(CURRENT DATE) AS VARCHAR(2))+'VAL'" should return M11Val
So I wish to get to:
SET M11Val = TMP.SALES_THIS_MTD
I have not tried EXECUTE IMMEDIATE yet. I guees this is a general question to the SQL syntax.
In advance, Thank you.
Regards,
pjerling
I was wondering whether it is possible to update data via SQL by building the column you wish to update in the same query.
For example, I have a spreadsheet that needs imported/updated on a daily basis. The spreadsheet contains one value, e.g. Sales This Month. The database have 12 columns, M1Val, M2Val, .., M12Val. Where the value indicate the month no, e.g. 12 = Dec in this instance.
UPDATE DBA.SALES
SET 'M'+CAST( MONTH(CURRENT DATE) AS VARCHAR(2))+'VAL' = TMP.SALES_THIS_MTD
FROM DBA.SALES LEFT OUTER JOIN DBA.TMP_SALES TMP ON SALES.ACCT_NUMBER = TMP.SOURCE_ID
;
COMMIT
;
Current Month = 11
Therefore "'M'+CAST( MONTH(CURRENT DATE) AS VARCHAR(2))+'VAL'" should return M11Val
So I wish to get to:
SET M11Val = TMP.SALES_THIS_MTD
I have not tried EXECUTE IMMEDIATE yet. I guees this is a general question to the SQL syntax.
In advance, Thank you.
Regards,
pjerling
You can always create a temp table, move the data to temp, dump the original and recreate the original table with the new column and the old table name.
As much as I see you are using Sybase ASA, you ca do it by EXECUTE IMMEDIATE:
Syntax 1
EXECUTE IMMEDIATE [ execute-option ] string-expression
execute-option:
WITH QUOTES [ ON | OFF ]
| WITH ESCAPES { ON | OFF }
| WITH RESULT SET { ON | OFF }
Syntax 2
EXECUTE ( string-expression )
Syntax 1
EXECUTE IMMEDIATE [ execute-option ] string-expression
execute-option:
WITH QUOTES [ ON | OFF ]
| WITH ESCAPES { ON | OFF }
| WITH RESULT SET { ON | OFF }
Syntax 2
EXECUTE ( string-expression )
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you for all the posts. I will be back at my desk tomorrow to try these out! :-)
ASKER
Thank you very much! Working perfect!
See http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.help.ase_15.0.commands/html/commands/commands56.htm