Chapter 10 – SQL INSERT and UPDATE
Section I – INSERT
When to use INSERT
The INSERT statement is used primarily in three instances. One is when you wish to enter a single row into a dataset. The second is when you wish to load the results of a SELECT statement all at once into a table. (In some systems, such as Access, you can actually create a table using an INSERT statement).
INSERT VALUES
The difference between the two INSERT statements revolves entirely around syntax. The individual insert relies on using the VALUES keyword. In the below examples we’re going to show the exact same INSERT statement but with a slight variation.
Proc SQL;
Insert Into Chapt10.Obj_Recorders
( ID,
Premise,
Name,
OpCo,
State,
MV90,
Active,
Interval,
StartDate,
LastUploadDate,
LastFinaledDate,
WeatherStationID)
Values
( “TESTID_1″
“”,
“My 1st Test ID”,
“IMP”,
“IN”,
“EAS”,
“T”,
15,
’01JAN2008′d,
’01JAN2008′d,
’01JAN2008′d,
“FWA”);
Quit;
In the rest of the examples, we’ll trim down the space for simplicity sake.
Proc SQL;
Insert Into Chapt10.Obj_Recorders (ID, Name, OpCo, State, MV90, Active, Interval, StartDate, LastUploadDate, LastFinaledDate, WeatherStationID)
Values (“TESTID_2″, “My 2nd Test ID”, “IMP”, “IN”, “EAS”, “T”, 15, ’01JAN2008′d, ’01JAN2008′d, ’01JAN2008′d, “FWA”);
Quit;
Proc SQL;
Insert Into Chapt10.Obj_Recorders
Values(“TESTID_3″,”",”My 3rd Test ID”,”IMP”,”IN”,”EAS”,”T”,15,’01JAN2008′d,’01JAN2008′d,’01JAN2008′d,”FWA”)
Values(“TESTID_4″,”",”My 4th Test ID”,”IMP”,”IN”,”EAS”,”T”,15,’01JAN2008′d,’01JAN2008′d,’01JAN2008′d,”FWA”);
Quit;
Note that whenever you’re inserting using the VALUES keyword you are only required to enter values where the column requires it. So in the 2nd example, we didn’t include the PREMISE column because there isn’t a NOTNULL constraint attached to it. In the third example we insert multiple rows into the table without specifying the column names or order. If you utilize this method, you must be ABSOLUTELY sure to have the order of your values the same as the order of the columns.
INSERT SELECT
The 2nd type of INSERT occurs when we wish to insert a result set from a query. In this instance, we use INSERT INTO SELECT… To show this, first let’s create a blank dataset using the CREATE statement, then we’ll fill it using the INSERT statement (yes, I know we could’ve just done it all in one statement, but then you wouldn’t have learned anything!)
Proc SQL;
Create Table Work.TNC_Recorders as
Select *
From Chapt10.Obj_Recorders
Where ID is missing;
Quit;
Proc SQL;
Insert Into Work.TNC_Recorders
Select *
From Chapt10.Obj_Recorders
Where OpCo = “TNC”;
Quit;
INSERT SET
The final way to use the INSERT statement is in conjunction with the SET statement. I won’t get into this method as it’s basically useless. It’s a very, VERY verbose way of writing the same as the VALUES style INSERT statement.
Section II – UPDATE
When to use UPDATE
The UPDATE statement is used to change values that already exist in the dataset. There are two ways to update a table, one is when the WHERE statement only depends on a single column or value, the other is when you’re attempting to update using multiple tables.
UPDATE x1
Let’s just go ahead with the simplest sample. Let’s update a single column regardless of constraints.
Proc SQL;
Update Chapt10.Obj_Recorders
Set LastFinaledDate = ’01JAN2008′d;
Quit;
I’m sure you noticed that EVERY row in the dataset was updated. In some instances, this is fantastic; in others, not so much. So let’s go ahead and constrain our update.
Proc SQL;
Update Chapt10.Obj_Recorders
Set Premise = “NoPremise”
Where Premise = “”;
Quit;
Now, just for posterity sake, let’s use an old friend to simplify this statement. First, let’s reset what we just did, then use the COALESCE statement.
Proc SQL;
Update Chapt10.Obj_Recorders
Set Premise = “”
Where Premise = “NoPremise”;
Quit;
Proc SQL;
Update Chapt10.Obj_Recorders
Set Premise = Coalesce(Premise, “NoPremise”);
Quit;
UPDATE Multiples
Ok, now let’s update columns in one table based on a query on another. First, let’s create a dummy table where we’re shifting some dates.
Proc SQL;
Create Table Work.ShiftDays as
Select Distinct ID,
LastUploadDate + 10 as NewUploadDate format=mmddyy10.
From Chapt10.Obj_Recorders
Where OpCo = “TNC”;
Quit;
Ok, now let’s update our original table based on our new dates.
Proc SQL;
Update Chapt10.Obj_Recorders as A
Set LastUploadDate = (Select NewUploadDate From Work.ShiftDays as B Where A.ID = B.ID)
Where ID in (Select Distinct ID From Work.ShiftDays as B);
Quit;
And lastly, we’ll do a more difficult one just for grins.
Proc SQL;
Create Table Work.ShiftDays as
Select Distinct
OpCo,
MV90,
Case OpCo
When “TNC” Then ’01JAN2009′d
When “TCC” Then ’01JAN2010′d
Else ’01JAN2008′d
End as NewUploadDate format=mmddyy10.
From Chapt10.Obj_Recorders;
Quit;
Proc SQL;
Update Chapt10.Obj_Recorders as A
Set LastUploadDate = (Select NewUploadDate From Work.ShiftDays as B Where A.Opco = B.OpCo and A.MV90 = B.MV90)
Where Exists (Select 1 From Work.ShiftDays as B Where A.Opco = B.OpCo and A.MV90 = B.MV90);
Quit;
In this query, we can use an EXISTS statement because we the WHERE subquery resolves to distinct row..
