SQL Update into table with case

Question

I am still new to DB stuff and this one is giving me an issue.

I am normalizing my table and I created a new one where [Status] is now FK.

I was able to insert all of the values from the original table into the new one, minus the [Status]

I am now trying to insert the [Status] using a select case.

Attempt 1:

USE [GESS]
GO

DECLARE @int_status int;
SELECT @int_status = 
    (CASE 
        WHEN [Status] = 'Alumni' THEN 5
        WHEN [Status] = 'Faculty' THEN 6
        WHEN [Status] = 'Friend' THEN 7
        WHEN [Status] = 'Student' THEN 8
        ELSE 9
    END)
FROM [dbo].[GESSContact]

INSERT INTO dbo.GESSContact2 ([Status]) VALUES (@int_status)
GO

But it says only 1 row is affected.

Attempt 2:

USE [GESS]
GO

INSERT INTO [dbo].[GESSContact2]([Status])
    Select (
    CASE 
        WHEN o.[Status] = 'Alumni' THEN 5
        WHEN o.[Status] = 'Faculty' THEN 6
        WHEN o.[Status] = 'Friend' THEN 7
        WHEN o.[Status] = 'Student' THEN 8
        ELSE 9
    END) As [Status]
From [dbo].[GESSContact] o
GO

This one says that all of the rows are affected but the [Status] is still set to null when I check.

Thanks for any help/suggestions

UPDATE:

While people were helping me out, The response that I marked as an answer made me realize I needed to be doing an UPDATE instead of INSERT. Here is my SQL that I used to make this work.

USE [GESS]
GO

UPDATE [dbo].[GESSContact2]
SET [dbo].[GESSContact2].[Status] = 
    CASE  
        WHEN o.[Status] = 'Alumni' THEN 5
        WHEN o.[Status] = 'Faculty' THEN 6
        WHEN o.[Status] = 'Friend' THEN 7
        WHEN o.[Status] = 'Student' THEN 8
        ELSE 9
    END 
FROM [dbo].[GESSContact2] JOIN [dbo].[GESSContact] o
ON [dbo].[GESSContact2].id = o.id

Thank you to everyone who helped!

In table dbo.GESSContact2 ([Status]) how many columns are there? Can i get the table definition? And do you have the data like 5,6,7,8,9 in the status table as it's used as a foreign key to dbo.GESSContact2 table? — Rakib, Aug 29 '17 at 16:26
I added the table def to the original post. In the status table, (5,6,7,8,9) are primary keys. In the original table, they are string values — Christian4423, Aug 29 '17 at 16:34
I need this table [dbo].[GESSContact2] definition. I just need number of columns and column names — Rakib, Aug 29 '17 at 16:36
There are 50 columns, named [id],[FirstName],[MiddleInitial],[LastName],[MaidenName],[Suffix],[Title],[FullName],[SpouseName],[Employer],[Advisor],[Status],[Donor],[Degree1],[Degree2],[Degree3],[Graduation1],[Graduation2],[Graduation3],[Major],[Minor],[Specialty],[Address],[AddressSecond],[City],[State],[Zipcode],[Country],[Email],[Phone],[Address2],[Address2Second],[City2],[State2],[Zipcode2],[Country2],[Email2],[Phone2],[Address3],[Address3Second],[City3],[State3],[Zipcode3],[Country3],[Email3],[Phone3],[Confirmed],[EmailOnly],[Newsletter],[Deceased] — Christian4423, Aug 29 '17 at 16:39

score 1 · Answer 1 · answered Aug 29 '17 at 16:24

1

This should work

INSERT INTO [dbo].[GESSContact2]([Status])
Select 
    CASE 
        WHEN o.[Status] = 'Alumni' THEN 5
        WHEN o.[Status] = 'Faculty' THEN 6
        WHEN o.[Status] = 'Friend' THEN 7
        WHEN o.[Status] = 'Student' THEN 8
        ELSE 9
    END 
From [dbo].[GESSContact] o

answered Aug 29 '17 at 16:24

ScaisEdge

131,976
10
91
107

Same result as before.. It says that the rows are updated but the [Status] is still null – Christian4423 Aug 29 '17 at 16:35
1

have you tried using this query in a normal db console and not in trigger ?? – ScaisEdge Aug 29 '17 at 16:43

Rakib · Accepted Answer · 2017-08-29T16:53:27.960

i guess i found the problem. Your id is not null so you will have to provide incremental values for id column or you will have to make id column auto increment.

Go to sql server management studio. Right click on table then go to design mode and select identity true from the property area.

In case you need some help to add identity follow this one: Auto increment primary key in SQL Server Management Studio 2012

And use following query to insert:

INSERT INTO [dbo].[GESSContact2]([Status])
Select 
    CASE 
        WHEN o.[Status] = 'Alumni' THEN 5
        WHEN o.[Status] = 'Faculty' THEN 6
        WHEN o.[Status] = 'Friend' THEN 7
        WHEN o.[Status] = 'Student' THEN 8
        ELSE 9
    END 
From [dbo].[GESSContact] o

This post just made me realize I am doing the wrong thing. In my OP I said I already added all of the columns from the original table into this new one. I need to do an update, not an insert... (face palm) — Christian4423, Aug 29 '17 at 16:52

SQL Update into table with case

2 Answers2