SQL Advanced Skills: Useful SQL statements

1. For example, there are two tables A (Field 1, Field 2, Field 3) and B (Field 2, Field 3). the stored data is as follows:
Table

Field 1	Field 2	Field 3
1	<Null>	Location content 1
1	<Null>	Location content 1
2	<Null>	Location content 2
2	<Null>	Location content 2
3	<Null>	Location 3
4	<Null>	Location content 4
5	<Null>	Location content 5
5	<Null>	Location content 5
5	<Null>	Location content 5
6	<Null>	Location 6
7	<Null>	Location content 7
8	<Null>	Lot content 8
8	<Null>	Lot content 8
8	<Null>	Lot content 8
8	<Null>	Lot content 8

Table B

Field 2	Field 3
Wdz1	Location content 1
Wdz2	Location 3
Wdz3	Location content 5
Wdz4	Location 6
Wdz5	Location content 7
Wdz6	Lot content 8

Use the following SQL statement (you can enter Field 2 in Table A with the same field 3 as Field 3 in Table B to Field 2 in Table)
Update Table 1 set field 1 = (select Field 1 from table 2 where Table 1. Field 3 = TABLE 2. Field 3)
The results are as follows:
Table A results

Field 1	Field 2	Field 3
1	Wdz1	Location content 1
1	Wdz1	Location content 1
2	<Null>	Location content 2
2	<Null>	Location content 2
3	Wdz2	Location 3
4	<Null>	Location content 4
5	Wdz3	Location content 5
5	Wdz3	Location content 5
5	Wdz3	Location content 5
6	Wdz4	Location 6
7	Wdz5	Location content 7
8	Wdz6	Lot content 8
8	Wdz6	Lot content 8
8	Wdz6	Lot content 8
8	Wdz6	Lot content 8

The following are the comments on this SQL statement.
I. Jiang Qifan"
Update Table 1 set field 1 = (select Field 1 from table 2 where Table 1. Field 3 = TABLE 2. Field 3)
The efficiency is very low. If there are X records, the sub-query will be executed X times.
If we do this, we can make full use of the index (if both tables have an index on Field 3) and avoid unnecessary multiple executions.
Update Table 1 set table 1. Field 2 = TABLE 2. Field 2 from table 1 inner join table 2 on table 1. Field 3 = TABLE 2. Field 3

The comments of netizens are summarized.

2. For example, if Table A in Table 1st is the same, the SQL statement for querying duplicate data in Table A is as follows:
Select Field 1 from Table A group by field 1 having count (Field 3)> 1
The query result is as follows:
Table A query results

Field 1

1

2

5

8

3. For example, to query the X1.dbf file in a fixed physical path in SQL Server, use the following SQL statement (you must install the VF driver before using this SQL statement)
Select * from OpenDataSource ('vfpoledb. 1', 'user ID =; DSN =; Collating Sequence = MACHINE; Data Source = C: \ dbfFiles \ X1.dbf; Password =; Cache Authentication = False; Mask Password = False; mode = Share Deny None; Extended Properties =; Encrypt Password = false ')... x1
Note: the query of other non-vfdatabase files is similar. You only need to set the parameters in OpenDataSource. Parameter description: OpenDataSource (data source driver name, data source connection string)