How to append to a existing record in SQL?

Question

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:

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.

Answer 1

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

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

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

Answer 2

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'

Answer 3

in Oracle, the string concatenation goes like this:

field1 = field1 || '12345'

Answer 4

If you are working with MySQL the query can be simplified as

UPDATE table set field1 = CONCAT('123456', field1) where field2 = '12' 

Answer 5

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

example 1 w/integers:

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:

example 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: