Updates to join table queries and table updates

Updates to join table queries and table updates

Background:

I wrote a blog about database join Table query, "read database -- (6) Connection". It mainly talks about the connection type and how to use the connection for multi-Table query. This blog is extended on this basis. First, let's look at an example of a join Table query.

USE TestUnion -- create table StaffUnion (id int primary key, name varchar (10) not null, comment VARCHAR (30), remark INT) -- employee table create table Staff (id int primary key, PersonNAME VARCHAR (10) not null, StaffUnionID INT, comment VARCHAR (30), remark INT) -- create table StaffPlan (id int primary key, StaffID INT, PlanName VARCHAR (10) not null, PlanContent VARCHAR (60), remark INT)

Note: No foreign key constraints are added to the three tables, so it is convenient to insert and delete data.

Insert several simple data records:

Insert into staffUnion VALUES (1, 'test1', 'union of employees', 1); insert into staffUnion VALUES (2, 'test2', 'employee set 2', 1 ); insert into staffUnion VALUES (3, 'test3', 'employee set 3', 1); insert into Staff VALUES (1, 'zhang san', 1, 'none', 1 ); insert into Staff VALUES (2, 'Li si', 1, 'none', 1); insert into Staff VALUES (3, 'wang wu', 2, 'none', 1 ); insert into Staff VALUES (4, 'lilim', 2, 'none', 1); insert into StaffPlan VALUES (, 'scheduler scheduler ',' Combined with annual information, preparation plan ', 1); insert into StaffPlan VALUES (, 'Build the environment', 'Build the environment, plan for five hrs', 1); insert into StaffPlan VALUES, 'prepare the scheduler ', 'prepare the scheduler based on the annual information', 0 );

Requirements:

Change the preparation plan corresponding to Michael Jacob, who is a set of employees, to a financing plan, and use valid data, that is, remark = 1.

The corresponding SQL statement is:

UPDATE StaffPlan SET StaffPlan. planName = 'funding scheduler 'FROM StaffUnion inner join Staff ON Staff. staffUnionID = StaffUnion. idinner join StaffPlan ON StaffPlan. staffID = Staff. id where StaffPlan. ID = 1AND StaffPlan. remark = 1

Result:

StaffPlan table

If this method is not used, join Table query is performed first.

SELECT staffPlan. ID, staffPlan. planName, staffPlan. planContent, StaffPlan. remark FROM staffPlan inner join staff ON Staff. ID = StaffPlan. staffIDINNER JOIN StaffUnion ON staffUnion. ID = Staff. staffUnionID WHERE staffPlan. planname = 'scheduler scheduler 'AND StaffPlan. remark = '1'

Check this data and update it again.

UPDATE staffplan SET staffplan. planname = 'funding plan' WHERE staffPlan. planname = 'scheduler scheduler 'AND StaffPlan. remark = '1'

Comparative analysis:

Two SQL statements are required to update a join Table query. When the data volume is large, errors may occur.