Im trying to concat 4 or so columns in a SQL Table into a new column in the same SQL Table. I need to Alter the origional table and not create a view. Im using Azure Data Studio, however i have SSMS too

For example here is what my data looks like (currently they are all nvarchar (50) NULL

And here is what i want

Ive tried many queries but i just cant get it too work, the following code combines them fine, except if there is null values in one of the columns the unique id column is null,

UPDATE [dbo].TestTable SET NEWUNIQUEIDCOLUMNNAME = ([dbo].TestTable.ColumnA [dbo].TestTable.ColumnB [dbo].TestTable.ColumnC [dbo].TestTable.ColumnD)

It also needs to be able to if its null have a set number of characters in it ie ColumnB if null should be 6x0 ie. 000000 whereas ColumnD needs to only be 2x0 ie 00

Also,

the reason why im doing it this way is because there is no unique ID column that isnt null at some point in the data table that can be referenced and i cannot use a accruing INT as i need to be able to create the unique id in the input app, creating a uniqueIDcolumn this way will work and they will all have unique values as there is no null rows in all of them at once and each rows ID will be different.

UPDATE [dbo].TestTable SET NEWUNIQUEIDCOLUMNNAME = ([dbo].TestTable.ColumnA [dbo].TestTable.ColumnB [dbo].TestTable.ColumnC [dbo].TestTable.ColumnD)

I expected this code to just work. Only been using SQL for 6months.

CodePudding user response：

you can use isnull function to replace null as your expected value, like below:

UPDATE [dbo].TestTable SET NEWUNIQUEIDCOLUMNNAME = ([dbo].TestTable.ColumnA   isnull([dbo].TestTable.ColumnB,'00000')   [dbo].TestTable.ColumnC   isnull([dbo].TestTable.ColumnD,'00')

CodePudding user response：

Initial table is a bad design. However to fix the issue, you can generate an INT sequence to create a unique id in the new column. If not, do you plan to run update statement after each new row, so to generate a unique id for each new row ?