Using CASE in MS SQL

Code to update a field using “CASE” (instead of using if-then-else):

update
 [dbo].[mytable]
set
 [dbo].[mytable].[title_type]=
 (
 case
 when [title] = 'Baseline' then '0'
 when [title] = 'No Change' then '1'
 else '2'
 end
 );

SQL Duplicate a Table

How to duplicate a table – structure and data – in MS SQL server:

select * into [NewTable] from [OldTable]

In MySQL:

CREATE TABLE [NewTable] LIKE [OldTable];
INSERT [NewTable] SELECT * FROM [OldTable];

MS-SQL “UPDATE”

How to use Update in MS SQL Server:

UPDATE 
[dbo].[TEST_Scen_Years]
SET 
[dbo].[TEST_Scen_Years].[TAE] = [dbo].[FinalHistYear_TPD_Train].[TrnCnt]
FROM
[dbo].[TEST_Scen_Years] 
INNER JOIN 
[dbo].[FinalHistYear_TPD_Train] 
ON 
([dbo].[TEST_Scen_Years].[Scenario_Year] = [dbo].[FinalHistYear_TPD_Train].[Year]) 
AND 
([dbo].[TEST_Scen_Years].[Subdiv_Part] = [dbo].[FinalHistYear_TPD_Train].[Subdiv_part])
WHERE ((([dbo].[FinalHistYear_TPD_Train].[Train_Code])='A') AND (([dbo].[FinalHistYear_TPD_Train].[TrnCnt])>0));

MySQL Working with Nulls

Some useful MySQL commands:

How to find rows with null data and delete them:

delete from corridor where wb_id is null;

Note that this WON’T work:

delete from corridor where wb_id = null;

How to update a blank field:

update corridor set corr='MC' where corr is null;