MySQL - Chaining a Series of SQL commands

[DougieC]

Hello all,

I am still relatively new to MySQL and databases in general. I am working on populating a mastertable for Human Resource and have developed a series of MySQL commands to accomplish this. I would like to automate this as much as possible.

Is there a way to link several different SQL commands together?

Many commands rely on the command prior to it.

Just for reference, these are the commands that I am using.

(Find new records and insert into Mastertable)
insert into `mastertable2`
select 0302_2500.ssn, 0302_2500.vsssn, 0302_2500.name_ind, 0302_2500.pay_gr_aa, '0302', '30000'
from Mastertable2 right join 0302_2500 on mastertable2.ssn = 0302_2500.SSN
where (((if(mastertable2.ssn=0302_2500.ssn, 0, 1))=1))

(Find records with changes and insert into Transition table)
insert into transition2
select 0302_2500.ssn, 0302_2500.vsssn, 0302_2500.name_ind, 0302.pay_gr_aa, '0302', '1001'
from 0302_2500, mastertable2
where mastertable2.ssn=0302_2500.ssn
and ((((if(mastertable2.vsssn=0302_2500.vsssn, 0, 1))=1))
or (((if(mastertable2.name_ind=0302_2500.name_ind, 0, 1))=1)))

(Match Mastertable to transition and update mastertable Date_end)
Update `mastertable2`, transition2
set mastertable2.date_end=(transition2.date_start-1)
where mastertable2.ssn=transition2.ssn
and mastertable2.date_end=transition2.`Date_end`

(Insert changed records of transition table into mastertable)
Insert into `mastertable2`
select * from `transition2`

(Delete records from transition table)
Delete from transition2 where ssn>0

[r035198x]

Hello all,

I am still relatively new to MySQL and databases in general. I am working on populating a mastertable for Human Resource and have developed a series of MySQL commands to accomplish this. I would like to automate this as much as possible.

Is there a way to link several different SQL commands together?

Many commands rely on the command prior to it.

Just for reference, these are the commands that I am using.

(Find new records and insert into Mastertable)
insert into `mastertable2`
select 0302_2500.ssn, 0302_2500.vsssn, 0302_2500.name_ind, 0302_2500.pay_gr_aa, '0302', '30000'
from Mastertable2 right join 0302_2500 on mastertable2.ssn = 0302_2500.SSN
where (((if(mastertable2.ssn=0302_2500.ssn, 0, 1))=1))

(Find records with changes and insert into Transition table)
insert into transition2
select 0302_2500.ssn, 0302_2500.vsssn, 0302_2500.name_ind, 0302.pay_gr_aa, '0302', '1001'
from 0302_2500, mastertable2
where mastertable2.ssn=0302_2500.ssn
and ((((if(mastertable2.vsssn=0302_2500.vsssn, 0, 1))=1))
or (((if(mastertable2.name_ind=0302_2500.name_ind, 0, 1))=1)))

(Match Mastertable to transition and update mastertable Date_end)
Update `mastertable2`, transition2
set mastertable2.date_end=(transition2.date_start-1)
where mastertable2.ssn=transition2.ssn
and mastertable2.date_end=transition2.`Date_end`

(Insert changed records of transition table into mastertable)
Insert into `mastertable2`
select * from `transition2`

(Delete records from transition table)
Delete from transition2 where ssn>0

That can now be done by grouping the statements into a procedure.

[DougieC]

That can now be done by grouping the statements into a procedure.

Thanks, thats exactly what I needed. I believe that my biggest problem is figuring out what everything is called :) thanks for the help.

[r035198x]

Thanks, thats exactly what I needed. I believe that my biggest problem is figuring out what everything is called :) thanks for the help.

The more you write the codes the more terms you'll get to know.