英文:
Is there a keyword for getting existing data in a record?
问题
我想一次性更新表中的所有记录，并在每个记录的条目前后添加括号。
在这个空白处，我能加入什么来帮助我实现相同的目标吗？
英文:
I want to update all the records in my table at once and add brackets before and after each entry in the record.
Is there anything I can add in this blank which will help me do the same?
update @column_list set ListColumns = '['+ _________ +']'
I have not been able to figure anything out. I thought I'd try asking here once, or else I will have to update all records separately looking at their actual values and adding brackets.
答案1
得分: 1
我不知道你为什么想要用括号封装所有内容，但我脑海中浮现的做法是这样的：
UPDATE [tableName]
SET
[col1] = CONCAT('[',[col1],']')
,[col2] = CONCAT('[',[col2],']')
,...
如果你想要在插入或更新表本身时执行这个操作，你可以创建一个触发器：
CREATE TRIGGER [triggerName]
ON [tableName]
AFTER INSERT, UPDATE
AS
BEGIN
SET NOCOUNT ON;
IF TRIGGER_NESTLEVEL()>1
RETURN;
UPDATE [tableName]
SET
[col1] = CONCAT('[', [col1], ']')
,[col2] = CONCAT('[', [col2], ']')
,....
WHERE ID IN (SELECT ID FROM INSERTED);
END
然而，将类型为（n）varchar 的列设置为另一种数据类型列将会引发问题。
英文:
I don't know why you'd want to encapsulate everything in brackets, but what comes to my mind is doing something like this:
UPDATE [tableName]
SET
[col1] = CONCAT('[',[col1],']')
,[col2] = CONCAT('[',[col2],']')
,...
If you want to perform this action on insert or update to the table itself you can create a trigger
CREATE TRIGGER [triggerName]
ON [tableName]
AFTER INSERT, UPDATE
AS
BEGIN
SET NOCOUNT ON;
IF TRIGGER_NESTLEVEL()>1
RETURN;
UPDATE [tableName]
SET
[col1] = CONCAT('[', [col1], ']')
,[col2] = CONCAT('[', [col2], ']')
,....
WHERE ID IN (SELECT ID FROM INSERTED);
END
However, setting type (n)varchar into another data type column will cause issues.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论