發表者討論內容

冷日
(冷日) 發表時間：2019/5/27 5:49

Webmaster

註冊日: 2008/2/19
來自:
發表數: 15771

[轉貼]Update rows in SQL that retain the old value

Update rows in SQL that retain the old value [closed]

My table in SQL

CREATE TABLE Person(
  Person_id INT PRIMARYKEY
  Person_name NVARCHAR(250)
)

Data in table:

INSERT INTO Person VALUES(1,'Peter')

I want update row in SQL that retain the old value.

UPDATE Person SET Person_name = 'Mary' WHERE Person_id = 1

This command does not work. I want after update column Person_name is value 'Peter Mary'

You need to concatenate the literal value Mary with the value of the column Person_name using your RDBMS concatenation operator. But, you didn't specify what RDNMS you are using.

Here is the standard way:

UPDATE Person
SET Person_name = Person_name || ' ' || 'Mary'
WHERE Person_id = 1;

For SQL Server use the + operator like so:

UPDATE Person
SET Person_name = Person_name + ' ' + 'Mary'
WHERE Person_id = 1;

Is it this you want to archieve?

Before the update:

ID | Person_name
---+---------------
1  | Peter

After the update:

ID | Person_name
---+---------------
1  | Peter Mary

If you're for example using MySQL, you could use this approach:

UPDATE Person
SET Person_name = CONCAT(Person_name, ' ', 'Mary')
WHERE Person_id = 1

Just CONCAT a blank and the new value to the current value.
Please don't forget to add the RDBMS you're using. Other RDBMS might have different syntaxes and possibilities.

原文出處：Update rows in SQL that retain the old value - Stack Overflow

回覆

主題一覽的下方

討論串

前一個主題 | 下一個主題 | 頁首 |

| |

發表新主題

DB研討會 : [轉貼]Update rows in SQL that retain the old value