May 25, 2016 at 6:57 am
Trying to create a computed column that can be edit.
LASTNAME + FIRSTNAME
I'm trying to be able to edit the computed column (NICKNAME) = LASTNAME FIRSTNAME and change to their NICKNAME.
I get SQL Error #42000
---------------------------
[Microsoft][SQL Server Native Client 11.0][SQL Server]The column "NICKNAME" cannot be modified because it is either a computed column or is the result of a UNION operator.
---------------------------
OK
---------------------------
May 25, 2016 at 7:04 am
Computed columns can't be edited. There's no way to make one that you can edit to entered values.
If you want a column that starts as LASTNAME + FIRSTNAME but can be changed to something else later, then it's a normal varchar column with a default value.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 25, 2016 at 9:26 am
Thanks:-):-):-):-):-):-)! Much appreciated!
May 25, 2016 at 2:45 pm
franklinkeith (5/25/2016)
Trying to create a computed column that can be edit.LASTNAME + FIRSTNAME
I'm trying to be able to edit the computed column (NICKNAME) = LASTNAME FIRSTNAME and change to their NICKNAME.
If you need an editable "computed column" ...
Create a standard column [NICKNAME]
Create an INSERT trigger that sets [NICKNAME] = [LASTNAME] + ' ' + [FIRSTNAME]
Then the column is available to be updated as normal.
May 26, 2016 at 6:57 am
Thanks, I'm trying this now. :-):-)
May 26, 2016 at 7:09 am
DesNorton (5/25/2016)
If you need an editable "computed column" ...Create a standard column [NICKNAME]
Create an INSERT trigger that sets [NICKNAME] = [LASTNAME] + ' ' + [FIRSTNAME]
Then the column is available to be updated as normal.
You also want the trigger to fire on UPDATE, in case LASTNAME or FIRSTNAME ever change for a particular row.
John
May 26, 2016 at 7:34 am
John Mitchell-245523 (5/26/2016)
DesNorton (5/25/2016)
If you need an editable "computed column" ...Create a standard column [NICKNAME]
Create an INSERT trigger that sets [NICKNAME] = [LASTNAME] + ' ' + [FIRSTNAME]
Then the column is available to be updated as normal.
You also want the trigger to fire on UPDATE, in case LASTNAME or FIRSTNAME ever change for a particular row.
John
Depending on your business rules .....
If you do fire on UPDATE, you may need to confirm whether NICKNAME has been edited before, and then not change it.
If you really want to use computed columns, you could also try something like this, but then you have the potential of lots of nulls.
Also, note the use of COALESCE and not ISNULL. If FirstName+LastName is longer than 20 chars, ISNULL would truncate it. This is because the output of ISNULL has the same datatype as the first parameter.
CREATE TABLE dbo.TestTable (
ID INT IDENTITY(1,1) NOT NULL
, FirstName VARCHAR(50) NOT NULL
, LastName VARCHAR(50) NULL
, NickName VARCHAR(20) NULL
, VIRTUAL_NICKNAME AS COALESCE(NickName, FirstName + ISNULL(' ' + LastName, ''))
);
May 26, 2016 at 7:35 am
John Mitchell-245523 (5/26/2016)
DesNorton (5/25/2016)
If you need an editable "computed column" ...Create a standard column [NICKNAME]
Create an INSERT trigger that sets [NICKNAME] = [LASTNAME] + ' ' + [FIRSTNAME]
Then the column is available to be updated as normal.
You also want the trigger to fire on UPDATE, in case LASTNAME or FIRSTNAME ever change for a particular row.
John
You want to be careful here. You don't want to wipe out any edits to this field, just because the record was updated, especially if the update was to edit that field.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
May 26, 2016 at 7:41 am
DesNorton (5/26/2016)
Depending on your business rules .....If you do fire on UPDATE, you may need to confirm whether NICKNAME has been edited before, and then not change it.
drew.allen (5/26/2016)
You want to be careful here. You don't want to wipe out any edits to this field, just because the record was updated, especially if the update was to edit that field.
Mmm... that's a point. It would indeed depend on the business rules. It's a minefield!
John
May 26, 2016 at 8:42 am
Maybe it's just me but it seems like overcomplicating the matter. As Gail suggested just use a normal VARCHAR column which can be updated any time. This logic can be applied at the application layer or a stored procedure which is updating the table. A one time update can be done to existing rows if the column was added after the fact.
CREATE TABLE TestTable (ID INT IDENTITY, FirstName VARCHAR(50), LastName VARCHAR(50), NickName VARCHAR(101))
GO
CREATE PROCEDURE InsertTest @FirstName VARCHAR(50), @LastName VARCHAR(50), @NickName VARCHAR(101) = NULL AS
IF @NickName IS NULL
INSERT INTO dbo.TestTable( FirstName, LastName, NickName )
VALUES ( @FirstName, @LastName, CONCAT(@LastName,' ',@FirstName))
ELSE
INSERT INTO dbo.TestTable( FirstName, LastName, NickName )
VALUES ( @FirstName, @LastName, @NickName)
GO
dbo.InsertTest @FirstName = 'Joe', @LastName = 'Someone'
GO
dbo.InsertTest @FirstName = 'Jane', @LastName = 'Doe', @NickName = 'Awesome'
SELECT * FROM dbo.TestTable
DROP TABLE dbo.TestTable
DROP PROCEDURE dbo.InsertTest
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply