1
00:00:04,790 --> 00:00:10,610
If you don't already have SQL Server Management Studio open, please go ahead and open it now.

2
00:00:10,610 --> 00:00:15,170
We have briefly covered the insert statement previously in this course, but there's a few more things

3
00:00:15,170 --> 00:00:16,400
that we need to cover.

4
00:00:16,400 --> 00:00:19,580
I'll just use the customers table and right click.

5
00:00:19,580 --> 00:00:22,940
And we can use the script table insert template.

6
00:00:22,940 --> 00:00:26,840
And I'll just open a new query window and this will be a starting point.

7
00:00:26,840 --> 00:00:30,530
This template is an example of a value list insertion.

8
00:00:30,530 --> 00:00:32,840
So here we have the column list.

9
00:00:32,840 --> 00:00:37,280
And then we have a list of values that are matched up with the columns.

10
00:00:37,280 --> 00:00:40,910
So here we have the customer ID which is the first in the values list.

11
00:00:40,910 --> 00:00:46,580
And whatever value we put in there is going to go into the customer ID column and so forth and so on.

12
00:00:46,580 --> 00:00:53,750
It's important to note that these columns do not have to be in any certain order, as long as the values

13
00:00:53,750 --> 00:00:57,530
are going to match the column that they're going to go into.

14
00:00:57,530 --> 00:01:03,860
Now, if we take a look at our primary key in the customers table, I'll right click and do properties.

15
00:01:03,860 --> 00:01:08,930
We're going to see that the primary key is not an auto increment.

16
00:01:08,930 --> 00:01:13,250
And so we are going to have to supply a correct value.

17
00:01:13,250 --> 00:01:14,480
It has to be unique.

18
00:01:14,480 --> 00:01:20,780
And so what I'm going to do is just right click and select top 1000 rows.

19
00:01:20,780 --> 00:01:29,090
And then if we want to see what the largest customer ID is, we can just order by customer ID and then

20
00:01:29,090 --> 00:01:32,000
that is going to be descending and executing.

21
00:01:32,000 --> 00:01:35,690
That should give us the highest customer ID.

22
00:01:35,690 --> 00:01:40,580
And so our inserts have to be greater than 665.

23
00:01:40,580 --> 00:01:46,430
And so going back to our insert template we'll just plug in some numbers here.

24
00:01:46,430 --> 00:01:49,340
So I'll use 667 for the ID.

25
00:01:49,340 --> 00:01:54,980
And then we will put in Jerry Garcia and our address.

26
00:01:54,980 --> 00:01:57,110
I'm just going to make some stuff up here.

27
00:01:57,110 --> 00:01:58,880
We'll just do a zip code here.

28
00:01:58,880 --> 00:02:00,830
That is a varchar as well.

29
00:02:00,830 --> 00:02:05,240
So we have to put both the zip code and the phone number as strings.

30
00:02:05,240 --> 00:02:05,900
There we go.

31
00:02:05,900 --> 00:02:08,360
And then we can just hit execute.

32
00:02:08,360 --> 00:02:09,890
We got one row affected.

33
00:02:09,890 --> 00:02:15,110
So if we go back and rerun this now Jerry Garcia is on top.

34
00:02:15,110 --> 00:02:23,090
And going back to our SQL query, you do not have to put in a list of all the columns, as long as you

35
00:02:23,090 --> 00:02:27,680
are putting them in the order in which they were created in the table.

36
00:02:27,680 --> 00:02:34,280
So when we use the script templates, it will automatically put them in the order that the fields were

37
00:02:34,280 --> 00:02:35,540
created in the table.

38
00:02:35,540 --> 00:02:38,990
And so we can do another example here.

39
00:02:38,990 --> 00:02:43,220
I'm just going to copy this, and I'll just open a new query window.

40
00:02:43,220 --> 00:02:47,420
And I will just get rid of the column list.

41
00:02:47,420 --> 00:02:50,120
And of course I have to go change these.

42
00:02:50,120 --> 00:02:52,910
And I'm going to put everything up on one line.

43
00:02:52,910 --> 00:02:54,980
So it's kind of easier to see.

44
00:02:54,980 --> 00:02:59,030
So here I could just put some different values here.

45
00:02:59,030 --> 00:03:06,170
And as long as we have all the columns and in the correct order, we can just hit execute and it will

46
00:03:06,170 --> 00:03:07,940
put in the new values.

47
00:03:07,940 --> 00:03:10,610
So we don't have to have the column names in there.

48
00:03:10,610 --> 00:03:12,020
And we'll just go check that.

49
00:03:12,020 --> 00:03:13,250
There's Thomas Covenant.

50
00:03:13,250 --> 00:03:17,450
So for the next example we'll go to the production database.

51
00:03:17,450 --> 00:03:23,270
From previous assignments we made most of these tables with an ID field that was autoincrement.

52
00:03:23,270 --> 00:03:27,350
But we haven't put anything into the staff table, so it's still blank.

53
00:03:27,350 --> 00:03:34,760
So what I want to do is modify the ID field on staff to be autoincrement because we haven't touched

54
00:03:34,760 --> 00:03:35,150
it yet.

55
00:03:35,150 --> 00:03:38,210
So we'll go to identity specification.

56
00:03:38,210 --> 00:03:42,410
We'll make it an Is identity table and then we will save that.

57
00:03:43,060 --> 00:03:50,080
And then I'll just close this and we will access our insert template.

58
00:03:50,110 --> 00:03:52,750
Notice that it does not show the ID field.

59
00:03:52,750 --> 00:03:58,810
And that's because it knows that you do not have to add data to that, because it's going to take care

60
00:03:58,810 --> 00:03:59,110
of it.

61
00:03:59,110 --> 00:04:01,630
So it does not list the ID field.

62
00:04:01,630 --> 00:04:04,480
And we'll just give it a last name here.

63
00:04:04,480 --> 00:04:08,200
And then we have to pick a role ID from our roles table.

64
00:04:08,200 --> 00:04:09,760
So let's just take a quick look.

65
00:04:09,760 --> 00:04:12,340
And one is operator two is repairman.

66
00:04:12,340 --> 00:04:15,760
We will make Mr. James Smith a repairman.

67
00:04:15,760 --> 00:04:17,650
And we can just hit execute.

68
00:04:17,650 --> 00:04:24,850
And going back to our staff query, we have now one staff member, James Smith, who is role ID two,

69
00:04:24,880 --> 00:04:26,680
which is a repairman.

70
00:04:26,680 --> 00:04:31,720
So for our last insert example, let's take a look at our production defect tracking table.

71
00:04:32,020 --> 00:04:34,570
I'm going to right click and go into the design mode.

72
00:04:34,570 --> 00:04:42,220
Now as originally designed the operator comments field will allow you to put in a null value as well

73
00:04:42,220 --> 00:04:46,150
as the supervisor ID, supervisor notes, and supervisor station.

74
00:04:46,150 --> 00:04:49,780
So the initial insert of a record requires all these other fields.

75
00:04:49,780 --> 00:04:56,470
But if you think about our production process, it's the operator that initially enters the record.

76
00:04:56,470 --> 00:05:01,600
And at that time we don't know the repairman or what station they're working at or their notes.

77
00:05:01,600 --> 00:05:03,700
And we also don't know the root cause.

78
00:05:03,700 --> 00:05:09,760
So we're going to make a couple of quick changes to allow nulls for these initially.

79
00:05:09,760 --> 00:05:14,530
And that way we can go ahead and have the operator enter the initial record.

80
00:05:14,530 --> 00:05:22,960
Also, let's go up to the ID field and let's make sure that it is set to identity specification.

81
00:05:22,960 --> 00:05:24,040
I'd set this earlier.

82
00:05:24,040 --> 00:05:29,050
So if yours is not set to this go ahead and make sure his identity is set to yes.

83
00:05:29,050 --> 00:05:31,480
And then we'll just save the table.

84
00:05:31,480 --> 00:05:34,300
Now we need to think about the defect time.

85
00:05:34,300 --> 00:05:39,370
This is a date time data type and we haven't encountered it before in this course.

86
00:05:39,370 --> 00:05:45,550
If you go to the course resources, there's a link to the get date function on the Microsoft site.

87
00:05:45,550 --> 00:05:50,530
And the purpose of this function is that it returns the current database system timestamp as a date

88
00:05:50,530 --> 00:05:51,460
time value.

89
00:05:51,460 --> 00:05:55,810
And so we can use this to get the current date time for our new record.

90
00:05:55,810 --> 00:05:58,390
And a quick way to show you how this works.

91
00:05:58,390 --> 00:06:03,760
I can open a new query window and just type select Get Date.

92
00:06:03,760 --> 00:06:10,300
Notice that the function turns kind of a pink or magenta color, and if I mouse over it, it tells you

93
00:06:10,300 --> 00:06:13,450
that this is a built in function and it returns a date time.

94
00:06:13,450 --> 00:06:17,770
So if I execute this, then we do get the current date and time.

95
00:06:17,770 --> 00:06:21,760
Now it stands to reason that we could just use this for the column value.

96
00:06:21,760 --> 00:06:28,030
But there's actually a better way if we go back to our defect tracking table definition and look at

97
00:06:28,030 --> 00:06:32,710
our defect time, there is a space to put a default value.

98
00:06:32,710 --> 00:06:38,800
And I could just simply put get date here as a function call and then save that.

99
00:06:38,800 --> 00:06:46,000
And so when a record is entered, if a value is not supplied for defect time, then it will make this

100
00:06:46,000 --> 00:06:48,130
function call to get the current date time.

101
00:06:48,130 --> 00:06:54,040
If you're entering a record with a date time in the past, of course, you can just use the insert statement

102
00:06:54,040 --> 00:06:55,600
to put a value in that field.

103
00:06:55,600 --> 00:07:00,370
But this way, no matter what, you will always get a valid value for our defect.

104
00:07:00,370 --> 00:07:00,820
Time.

105
00:07:00,820 --> 00:07:05,800
Now I'll make sure that this is saved, and I'm just going to close these tabs out.

106
00:07:05,800 --> 00:07:08,080
Now let's look at entering a record.

107
00:07:08,080 --> 00:07:12,340
We only have one operator here and they are a repair person.

108
00:07:12,340 --> 00:07:16,180
So we can do just a quick insert statement here.

109
00:07:16,180 --> 00:07:19,150
Insert into staff values.

110
00:07:19,150 --> 00:07:26,860
We don't have to put ID so we will do Ringwald Molly and then operators or ID one.

111
00:07:26,860 --> 00:07:30,670
And then I can just highlight this and hit execute.

112
00:07:30,670 --> 00:07:32,860
And then I'll highlight our select statement.

113
00:07:32,860 --> 00:07:35,680
And we now have an operator and a repairman.

114
00:07:35,680 --> 00:07:37,750
Now I'm going to comment this out here.

115
00:07:37,750 --> 00:07:41,170
And let's go over to our production defect tracking table.

116
00:07:41,170 --> 00:07:46,030
Right click and we'll go to Script Table Insert to New Query window.

117
00:07:46,030 --> 00:07:50,200
And looking at our template we don't need our defect time.

118
00:07:50,200 --> 00:07:51,700
We got to get rid of that comma.

119
00:07:51,700 --> 00:07:58,480
And we're not going to be entering anything except for the operator information and the defect information

120
00:07:58,480 --> 00:07:59,500
the initial record.

121
00:07:59,500 --> 00:08:05,020
So I'll just get rid of those columns and then I'll get rid of the same values.

122
00:08:06,150 --> 00:08:09,420
And so this is our template for our first insert.

123
00:08:09,420 --> 00:08:14,400
So for our operator ID that's going to be number two Molly Ringwald.

124
00:08:14,400 --> 00:08:16,320
So I'll just put a two here.

125
00:08:16,320 --> 00:08:20,310
And then the station ID let's just give it station number one.

126
00:08:20,310 --> 00:08:22,920
We can look at our categories.

127
00:08:22,920 --> 00:08:24,930
Assembly is the first station.

128
00:08:24,930 --> 00:08:27,240
So again we're going to put a one for that.

129
00:08:27,970 --> 00:08:34,840
And then the reason ID will just take a look at our reasons and let's just say it's incorrect Assembly.

130
00:08:34,840 --> 00:08:35,950
That's number two.

131
00:08:37,080 --> 00:08:39,900
And then for our comments, this is a varchar.

132
00:08:39,900 --> 00:08:45,420
Let's just say this is a test entry and I can just hit execute.

133
00:08:45,420 --> 00:08:48,450
Now let's go back and take a look.

134
00:08:48,450 --> 00:08:50,100
And we do have one record.

135
00:08:50,100 --> 00:08:56,340
There's our date and time, our operator ID, station ID, category ID and our comments.

136
00:08:56,340 --> 00:08:59,730
And we can see that NULL was displaced in the rest of the fields.

137
00:08:59,730 --> 00:09:06,990
And we will be using the update statement to update these as this defect moves through the process.

138
00:09:06,990 --> 00:09:13,260
Now let's take a look at how to use the update statement to update existing records.

139
00:09:13,260 --> 00:09:16,680
And for this we're going to go back to our battery Co database.

140
00:09:16,680 --> 00:09:19,380
Because we have a lot of data here we can play around with.

141
00:09:19,380 --> 00:09:25,980
And if you are just practicing queries, you might want to make a backup of a table so you don't mistakenly

142
00:09:25,980 --> 00:09:28,920
update records that shouldn't be updated.

143
00:09:28,920 --> 00:09:35,010
So we can use the select into statement to do that, and we'll just make a backup of the customers table.

144
00:09:35,010 --> 00:09:39,000
I'll hit new query and we will do select star into.

145
00:09:39,000 --> 00:09:41,400
And we're going to give it a new table name.

146
00:09:41,400 --> 00:09:45,690
So let's say customers update exercise.

147
00:09:45,690 --> 00:09:50,910
And this way if you're trying to formulate how to do a query we're not going to mess up existing data.

148
00:09:50,910 --> 00:09:53,250
And so we'll do from customers.

149
00:09:53,490 --> 00:09:57,090
And of course you could put a Where clause here and only get a subset.

150
00:09:57,090 --> 00:09:58,740
We're not going to worry about that.

151
00:09:59,160 --> 00:10:00,990
We'll just hit execute.

152
00:10:00,990 --> 00:10:03,600
And then of course we will refresh.

153
00:10:03,600 --> 00:10:05,520
And here is our table.

154
00:10:05,520 --> 00:10:07,350
And we can look at this table.

155
00:10:07,350 --> 00:10:09,000
And it's got all the records.

156
00:10:09,000 --> 00:10:11,880
So let's practice some updates here.

157
00:10:11,880 --> 00:10:19,710
Let's say that pseudocode here got married and her last name was updated to Miller.

158
00:10:19,710 --> 00:10:23,130
How could we update just this one record?

159
00:10:23,130 --> 00:10:28,440
Now when using an update statement or a delete statement, you do have the possibility of updating a

160
00:10:28,440 --> 00:10:30,420
lot of records that you didn't mean to.

161
00:10:30,420 --> 00:10:33,870
And so I kind of have a philosophy that I use.

162
00:10:33,870 --> 00:10:35,850
I call it select first.

163
00:10:35,850 --> 00:10:37,830
And it's a very simple idea.

164
00:10:37,830 --> 00:10:44,700
Before I update or delete data from a table, I create a Select statement that confines the data to

165
00:10:44,700 --> 00:10:50,430
only the records that I want to affect, just to make sure I'm not going to make a mistake if I only

166
00:10:50,430 --> 00:10:52,680
want to update the record.

167
00:10:52,680 --> 00:10:55,080
156 pseudocode.

168
00:10:55,080 --> 00:11:04,440
Then first I write a Select statement, select star from and I can just drag over this and then where

169
00:11:04,680 --> 00:11:09,090
cust id equals 156.

170
00:11:09,090 --> 00:11:16,740
And then if I execute this then I have constrained the Where clause to show only the record that I care

171
00:11:16,740 --> 00:11:17,070
about.

172
00:11:17,070 --> 00:11:22,110
Then the next part of this is I just turn my select statement into an update statement.

173
00:11:22,110 --> 00:11:28,740
So when we do the update statement, it is simply you do the update keyword and you put the name of

174
00:11:28,740 --> 00:11:33,000
the table that you want to update, and then you put in the new field values.

175
00:11:33,000 --> 00:11:42,270
So we can just do set L name equals miller where the customer ID equals 156.

176
00:11:42,270 --> 00:11:48,990
Now when you're doing this in the same query window and you're selecting this, you need to make sure

177
00:11:48,990 --> 00:11:50,730
that you select all of this.

178
00:11:50,730 --> 00:11:57,360
Because if I just selected this portion it would update every customer's last name to Miller, which

179
00:11:57,360 --> 00:11:59,040
is of course why we do a backup.

180
00:11:59,040 --> 00:12:01,620
I will highlight this hit execute.

181
00:12:01,620 --> 00:12:03,930
It will tell me how many rows were affected.

182
00:12:03,930 --> 00:12:14,040
And then of course I could just do a quick select statement again where cust id equals 156 and we select

183
00:12:14,040 --> 00:12:18,750
this and we can see that sue du code is now Sue Miller.

184
00:12:18,750 --> 00:12:25,260
So the update statement is pretty easy, but you almost always use some sort of Where clause.

185
00:12:25,260 --> 00:12:28,050
And you could use any of the clauses that we covered before.

186
00:12:28,050 --> 00:12:30,780
You could use relational operators, Boolean operators.

187
00:12:30,780 --> 00:12:36,780
You can have multiple where statements whatever you want, but it's important to use the select first

188
00:12:36,780 --> 00:12:40,740
method so that you're not updating data that you don't want to.

189
00:12:40,770 --> 00:12:46,470
Since we do have another backup table, let me just show you what would happen if you don't take care

190
00:12:46,470 --> 00:12:50,280
and use a Where clause to constrain the data that you're updating.

191
00:12:50,280 --> 00:12:53,310
So here of course, is our customer backup.

192
00:12:53,310 --> 00:13:03,600
And if I were just to say update and then customer backup set L name equal Miller.

193
00:13:03,600 --> 00:13:10,320
If I execute this basically it is going to set everybody's last name to Miller.

194
00:13:10,320 --> 00:13:13,230
And now I've just basically destroyed my data integrity.

195
00:13:13,230 --> 00:13:20,250
And so that is why you always use a Where clause to constrain your updates and your deletes.

196
00:13:20,250 --> 00:13:26,550
So let's look at another example where we're using the select first philosophy to make sure that we

197
00:13:26,550 --> 00:13:27,510
have the right records.

198
00:13:27,510 --> 00:13:35,340
Before we do an update, let's say that we want to give a raise to all of our employees that work in

199
00:13:35,340 --> 00:13:36,690
Department 300.

200
00:13:37,070 --> 00:13:41,390
And make less than $75,000 a year.

201
00:13:41,390 --> 00:13:49,640
So I'll open a new query window, and we will write a Select statement to select all employees who are

202
00:13:49,640 --> 00:13:54,620
in Department 300, and their salary is less than 75,000.

203
00:13:54,620 --> 00:13:59,180
And we'll execute this and make sure that our data is correct.

204
00:13:59,180 --> 00:14:04,100
Now notice that I did a select star which selects all columns.

205
00:14:04,100 --> 00:14:05,390
And then I did a comma.

206
00:14:05,390 --> 00:14:12,710
And I added a another column which is going to be salary times 1.1 which is 10% higher.

207
00:14:12,710 --> 00:14:14,450
And we're just calling it raise.

208
00:14:14,450 --> 00:14:20,390
So you can see the before and after values that our employees will have after the race.

209
00:14:20,390 --> 00:14:26,690
So now that I have my select statement that constrains my results to exactly the records that I want

210
00:14:26,690 --> 00:14:30,860
to update, then we can convert that into an update statement.

211
00:14:30,860 --> 00:14:39,320
Here we are updating the employees table and we're setting our salary equal to the current salary times

212
00:14:39,320 --> 00:14:46,160
110%, where our department ID is 300 and our salary is 7500.

213
00:14:46,160 --> 00:14:53,840
And so looking at Jennifer Litton, her salary will go from 58,009 30 to 64 823.

214
00:14:53,840 --> 00:14:58,550
If we execute this there should be seven rows that are updated.

215
00:14:58,550 --> 00:14:59,840
We'll hit execute.

216
00:14:59,840 --> 00:15:02,450
It did update those seven rows.

217
00:15:02,450 --> 00:15:11,390
And then if we look at our employees and we can just go find Jennifer very quickly where first name

218
00:15:11,390 --> 00:15:21,590
like and let's just try this like Jen there's Jennifer Litton and her salary is now $64,823.

219
00:15:21,590 --> 00:15:23,090
So she's got her raise.

220
00:15:23,120 --> 00:15:27,290
Now let's go back to our production defect tracking.

221
00:15:27,290 --> 00:15:32,840
And let's say now we want to update this record with repair information.

222
00:15:32,840 --> 00:15:37,610
So we want to enter repairman ID, station ID and repair notes.

223
00:15:37,610 --> 00:15:40,220
Those are the only columns that we want to update.

224
00:15:40,220 --> 00:15:43,730
So the key field our ID is a primary key field.

225
00:15:43,730 --> 00:15:46,610
We can use that to identify this record.

226
00:15:46,610 --> 00:15:51,320
Now yes of course there's only one record in here but we can still use that.

227
00:15:51,320 --> 00:15:57,470
So let's go to a new query window select star from Production Defect Tracking.

228
00:15:57,470 --> 00:16:00,170
And we can hit execute just to see what we have.

229
00:16:00,170 --> 00:16:05,450
And we'll go ahead and put our Where clause just so that we are constraining the records.

230
00:16:05,450 --> 00:16:08,270
And then we can write our update statement.

231
00:16:08,270 --> 00:16:13,490
So we will just say update production defect tracking.

232
00:16:13,490 --> 00:16:15,590
And then we'll use our set command.

233
00:16:15,620 --> 00:16:18,140
I can just pull those out here.

234
00:16:18,140 --> 00:16:21,740
So we'll do repairman ID equals.

235
00:16:21,740 --> 00:16:24,800
We'll have to go back to our staff.

236
00:16:24,800 --> 00:16:27,740
So our repairman is James Smith.

237
00:16:27,740 --> 00:16:29,300
He is ID number one.

238
00:16:29,300 --> 00:16:32,120
So we'll say set repairman ID to number one.

239
00:16:32,120 --> 00:16:33,590
And then we use a comma.

240
00:16:33,590 --> 00:16:39,680
And then our station that is also going to be a one because we're at the very front of the line we use

241
00:16:39,680 --> 00:16:40,610
another comma.

242
00:16:40,610 --> 00:16:42,440
And then our repair notes.

243
00:16:42,440 --> 00:16:49,610
We're just going to do a Varchar and just put in assembly defect test entry.

244
00:16:49,910 --> 00:16:53,330
And then we would do where ID equals one.

245
00:16:53,330 --> 00:16:58,580
And if we do an update here and then we can do our Select statement.

246
00:16:58,580 --> 00:17:03,350
And you can see that it did update our repair stations.

247
00:17:03,350 --> 00:17:06,830
Now let's go take a look at how to use the delete statement.

248
00:17:06,830 --> 00:17:08,690
We'll go back to Batterico.

249
00:17:08,810 --> 00:17:12,440
And we're going to go with our customers update exercise.

250
00:17:12,440 --> 00:17:16,550
So we're not actually pulling people out of the course database.

251
00:17:17,220 --> 00:17:24,090
And we'll just do a select star from table to get a look at the records.

252
00:17:24,090 --> 00:17:28,350
And let's say that we just want to delete Sue Miller.

253
00:17:28,350 --> 00:17:32,670
So again we have an ID field that uniquely identifies this record.

254
00:17:32,670 --> 00:17:40,290
If we want to delete Sue Miller, we could use the customer ID and using the select first we will do

255
00:17:40,290 --> 00:17:49,260
select star from customer update where customer ID equals 156.

256
00:17:49,590 --> 00:17:55,560
And we will make sure that it constrains it to only the records that we want to get rid of.

257
00:17:55,560 --> 00:18:07,800
And then we just say delete from customers update exercise where customer ID equals 156.

258
00:18:08,540 --> 00:18:10,820
And then highlight that and hit delete.

259
00:18:10,820 --> 00:18:13,250
It'll tell you how many rows are affected.

260
00:18:13,250 --> 00:18:18,680
If we go back and highlight our select statement, you'll see that that record has been removed.

261
00:18:18,680 --> 00:18:25,220
Now looking back at the table, you may ask, well, what if instead of using the customer ID, we just

262
00:18:25,220 --> 00:18:28,610
said where the first name equals Sam and the last name equals over?

263
00:18:28,640 --> 00:18:31,880
Well, it is possible to have two people with the same name.

264
00:18:31,880 --> 00:18:35,000
And if you do that, you run the risk of deleting the wrong person.

265
00:18:35,000 --> 00:18:42,890
So it's always good to do update and delete statements from a primary key field just to ensure that

266
00:18:42,890 --> 00:18:45,140
you have exactly the right record.

267
00:18:45,140 --> 00:18:48,380
Now let's go ahead and do one more example with delete.

268
00:18:48,380 --> 00:18:55,880
And one thing if I'm doing SQL queries and I do a delete statement, I'm always concerned that I'm mistakenly

269
00:18:55,880 --> 00:18:57,980
going to have it highlighted.

270
00:18:57,980 --> 00:19:02,150
And if I were to just highlight this and hit execute, it would delete everything in the table.

271
00:19:02,150 --> 00:19:04,970
So this is called a block comment.

272
00:19:04,970 --> 00:19:08,090
So it's a forward slash and then a star.

273
00:19:08,090 --> 00:19:12,740
And then when you're done with the block you can have as many lines as you want.

274
00:19:12,740 --> 00:19:14,630
Then you do star slash.

275
00:19:14,960 --> 00:19:18,680
And then you can see that it has commented that out.

276
00:19:18,680 --> 00:19:25,460
Now I could still highlight this and hit execute and it would still do the delete, but this at least

277
00:19:25,460 --> 00:19:28,040
gives me a visual cue to kind of stay away from it.

278
00:19:28,040 --> 00:19:34,160
And if I were to just hit execute, which would execute all SQL statements in this query window, I'm

279
00:19:34,160 --> 00:19:35,630
at least somewhat safe.

280
00:19:35,630 --> 00:19:41,360
So let's say that we want to delete all records from this table where they have an invalid zip code.

281
00:19:41,360 --> 00:19:50,030
Let's say that those are invalid customers, so we will do our select first select star from.

282
00:19:50,030 --> 00:19:53,390
And we're doing our update table just to be safe.

283
00:19:53,630 --> 00:19:56,690
And we'll say where zip.

284
00:19:56,690 --> 00:20:01,340
Not like it's just going to be zero through nine.

285
00:20:01,340 --> 00:20:08,120
And if you remember us zip codes are five numbers and they don't include letters or anything else.

286
00:20:08,120 --> 00:20:15,920
So this will find all zips that don't exclusively include five numbers.

287
00:20:15,920 --> 00:20:17,810
So we will execute that.

288
00:20:17,810 --> 00:20:21,080
And we can see that these results are correct.

289
00:20:21,080 --> 00:20:21,980
We've got one here.

290
00:20:21,980 --> 00:20:23,960
But that's only four characters.

291
00:20:23,960 --> 00:20:27,920
Those are only four characters to the rest have spaces and dashes and stuff like that.

292
00:20:27,920 --> 00:20:34,310
So now we know that we've constrained the result set to be only the records that we want, then we can

293
00:20:34,310 --> 00:20:37,460
convert this into a delete statement.

294
00:20:38,080 --> 00:20:40,780
And this is 13 rows.

295
00:20:40,810 --> 00:20:42,280
I'll hit execute.

296
00:20:42,580 --> 00:20:44,620
It affected 13 rows.

297
00:20:44,620 --> 00:20:50,230
And now I can change it back into a select and execute that.

298
00:20:50,230 --> 00:20:53,470
And we don't have any invalid zip codes.

299
00:20:53,470 --> 00:20:55,150
So we were successful.

300
00:20:55,240 --> 00:20:57,970
So next up we're going to be doing an assignment.

301
00:20:57,970 --> 00:21:00,880
You'll be doing inserts updates and deletes.

302
00:21:00,880 --> 00:21:08,350
And just remember for your script table there is also an update template that shows you how you can

303
00:21:08,350 --> 00:21:12,490
do multiple columns and where to put your search conditions.

304
00:21:12,490 --> 00:21:18,700
There is also the delete template that shows you a delete statement for your Where condition.

305
00:21:18,700 --> 00:21:23,530
So take a look at these as you tackle the next assignment and I'll see you there.