CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Jasmine
Jasmine

Reputation: 5326

query to update data in a table in SQL

I have a lookup table that has following data:

AlcoholStatus

Id  Name
0   None
1   Not Drunk
2   Drunk Less
3   Drunk Medium
4   Heavy Drinker

Another table reference this table data (ID) and so it acts as a foreign key in another table.

Now I want to update this Table to the following with just data update and delete two row. It gives me foreign key constraint. I did what I want but is there any simple way to achieve this? I also want to update the another table to appropriate values as follows:

AlcoholStatus

Id  Name
0   None
1   Yes
2   No

Here, Yes means 3,4 in the initial data and No means 1,2.

Please help me how to delete the two rows and update data as above and also update other table to either 1 (For 3,4) and 2 for (1,2).

Upvotes: 1

Views: 65

Answers (1)

gofr1
gofr1

Reputation: 15977

For SQL Server:

At first update table that has foreign key (I call it Alcoholics):

UPDATE Alcoholics
SET StatusId = CASE WHEN StatusId IN (1,2) THEN 2 
                    WHEN StatusID IN (3,4) THEN 1
                    ELSE 0 END

Then update AlcoholStatus table

UPDATE AlcoholStatus
SET Name = CASE WHEN id = 1 THEN 'Yes' 
                WHEN id = 2 THEN 'No' 
                ELSE Name END
WHERE Id IN (1,2)

Then delete statuses with id in (3,4)

DELETE FROM AlcoholStatus
WHERE id in (3,4)

Upvotes: 2

Related Questions