如何在SQL中追加现有记录?

Can someone tell me how to append in SQL? I've going around all day trying to figure this out. This is what I have so far:

有人能告诉我如何在SQL中追加吗?我花了一整天的时间试图弄明白这一点。这是我目前所拥有的:

update table1 
set field1 = field1 + '123456' 
where field2 = '12'

Sorry, I forgot to mention that I'm updating more than one field in the statement.

对不起，我忘了提到我正在更新语句中的多个字段。

4 个解决方案

#1

Your statement should work as long as field1 is not null or the data to be appended is not null.

只要field1不是null，或者附加的数据不是null，您的语句就应该有效。

Something like this could help in the case where field1 is null.

在field1为null的情况下，类似这样的事情可能会有所帮助。

update table1 set field1 = ISNULL(field1, '') + '123456' where field2 = '12'

#2

in Oracle, the string concatenation goes like this:

在Oracle中，字符串连接如下:

field1 = field1 || '12345'

#3

Your question is a bit confusing because you are saying append but your example is really just a set operation:

你的问题有点让人困惑，因为你说的是追加但是你的例子实际上是一个集合操作:

update table1 set field1 = '123456', field2 = '' where field2 = '12'

if you were actually appending it would depend on your database but lookup string concatenation for reference.

如果您要附加它，它将取决于您的数据库，但查找字符串连接作为参考。

update table set field1 = concat(field2, '3456') where field2 = '12'

#4

here are the differences between varchar concatenation and integer addition, you appear to have varchar concatenation going on, you may need to use CAST and CONVERT (Transact-SQL) to add your numbers

这里是varchar连接和整数加法之间的区别，您似乎有varchar连接，您可能需要使用CAST和CONVERT (Transact-SQL)来添加数字

example 1 w/integers:

示例1 w /整数:

DECLARE @table1 TABLE(field1 int, field2 int)
INSERT INTO @table1 VALUES (123456, 12)
SELECT 'before' as 'before', * FROM @table1

UPDATE @table1 SET field1 = field1 + 123456 WHERE field2 = 12
SELECT 'after' as 'after', * FROM @table1

example 1 results:

示例1的结果:

example 2 w/varchar:

示例2 w / varchar:

DECLARE @table2 TABLE(field1 varchar(50), field2 varchar(2))
INSERT INTO @table2 VALUES ('123456', '12')
SELECT 'before' as 'before', * FROM @table2

UPDATE @table2 SET field1 = field1 + '123456' WHERE field2 = '12'
SELECT 'after' as 'after', * FROM @table2

example 2 results:

例2的结果: