Some queries with insert statement.
All right, so let's have a look at the sub queries that can be used with insert statements.
So the insert statement, it uses the data returned from the sub query and then inserts it into another
table.
The selected data in the query can be modified with any of the characters, dates or no functions.
The data coming from the subquery will be used in the insert statement.
So here I have created a state province test table in the person schema.
So let's retrieve all the data from this table.
In this example, we are going to insert the data from the state province table to the state province
test table with the sub query.
And the query will retrieve state province code.
Country code.
Is only state province, flag name, territory, ID, modified date and send the data in these columns
to the insert statement, then the insert statement will insert these records into the state province
test table.
Now, he didn't specify this state province I.D..
Because this field has an auto increment feature.
And we can see this in the table design window.
So just click on the state province I.D. and then find the identity specification from the column properties.
Now, the value of it is yes.
And that means this property is active and this field has auto increment one by one.
That means because of this feature, when a new record is inserted into the table, this value will
automatically increase, right?
So we will not be allowed to specify a value for this field in the insert statement, even if we wanted
to.
And that, my friends, is why we didn't specify these state province Widefield, so before we execute
this query.
Let's retrieve all the data from the state province table.
And here are 181 rows in this table.
And all these rows will be inserted into the state province test table as soon as the query of the insert
statement has been executed.
And it looks like it's been completed successfully and 181 rows have been affected.
Now, let's retrieve data from the state province test.
Great, all the data has been inserted from the state province table into the state province test.
Well done.
Some queries with updates statement.
Now, the subquery can be used in conjunction with the update statement, either single or multiple
columns in a table can be updated when using a sub query with the update statement.
So in this example, we're going to update the territory idea of Canada in the state province test table
with the sub query and the Territory ID will be set to 99.
Now we must specify a condition to update a specific row, otherwise all tables will be updated.
The country region code equals CI A.
And if we execute the query, we will update the data that we want to, but in this example, we're
going to update it with some query and a different table.
So let's use a sub query instead of specifying a value for country region code.
And the outer query is asking for their country region code because of this reason, the sub query must
return the country region code from the state province table.
Right.
And the condition must be country region code equals CIA.
Now, make sure before executing the query.
Because if we exclude the subquery, only the code seet is going to be listed, right?
All right.
So that's good.
There are 13 rows.
And it looks like it is ready to execute, so let's.
Yeah, let's execute the query to update the data.
And here there are 13 rows that have been affected.
Now, if we select all data from the state province test table, we're going to see that all data within
the CIA code has been updated to 99.
Sub queries with the lead statement.
So the subquery can be used in conjunction with a delete statement, just like with any of the other
statements that we've mentioned before.
The sub query of the leading data from the table is very similar to the sub query to update data from
the table.
So in this example, we're going to delete data from the state province test table with a sub query.
If a where clause is not specified for deleting statement, all data from the table will be deleted.
So just make sure to clearly specify the data that you want to delete.
And we will delete a specific row and these are going to be in the sub queries.
We will get data from the state province table, and the commission part is very important because each
row in the same query will be deleted, so.
Yeah, we're going to delete rows, that is country region code equals s.A.
And let's check the subquery, see if it returns Rosett, you want.
Execute the subquery and sure, so here are the 13 rows that have been listed.
So that's good.
Everything looks OK, right?
So now we can execute the query and delete these rows.
The query has been successfully executed.
And now if we select all data from the state province test.
You'll notice that the specified 13 rows have been deleted.