Learner Learner - 1 year ago 94
SQL Question

query to update data in a table in SQL

I have a lookup table that has following data:


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:


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)

Answer Source

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)
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download