1

I need to write a procedure in mysql that runs every night and performs the following:-

For each 'id' from 'associate' table find if there is an entry in 'status table' for a particular 'department' . If there is an entry then move on to next 'id' else insert a row in status table for that id.

Associate Table

Assoc_Id

Assoc_Project

Assoc_Assigned_On

Status Table

Sub_id

Assoc_Id

Sub_on

Department

I just have no clue about how to do this. Any help will be highly appreciated.

Thanks in advance

Prix
  • 19,417
  • 15
  • 73
  • 132
user3368855
  • 51
  • 4
  • could you please at least provide relevant table structures ? – Akhil Sidharth Mar 01 '14 at 18:04
  • "Move on to next id." With respect, you're thinking procedurally when trying to figure out how to do something with a declarative language. Don't think "for-each", think about sets of values, and you'll have an easier time with SQL. – O. Jones Mar 01 '14 at 19:55

1 Answers1

1

Since you haven't provided a full schema, I'm unsure of primary/foreign keys on the tables, but here is a query that can help you:

INSERT INTO tbl_status (Assoc_Id, Sub_on, Department)
SELECT a.Assoc_Id, [Your Sub_on Value], 'IT'
FROM tbl_associate a
    LEFT JOIN tbl_status s ON a.Assoc_Id = s.Assoc_Id AND s.Department = 'IT'
WHERE s.Sub_Id IS NULL;

The basic idea here is to LEFT JOIN on the status table from the associate table where the Assoc_Id's are equal and the department value is the one you're searching for. The WHERE clause filters the results so that it only shows records that are not currently in the status table based on the join condition. I chose Sub_Id because I assumed that is the primary key. It doesn't really matter what you choose here except that the value has to be a non-null field in the status table order for this query to work.

jtate
  • 2,612
  • 7
  • 25
  • 35
  • Here is the detail about primary key & foreign key. **Associate Table** Assoc_Id (Primary Key) Assoc_Project Assoc_Assigned_On **Status Table** Sub_id (Primary Key) Assoc_Id (Foreign Key) Sub_on Department What I want my procedure to do is to take each AssociateId from Associate table and insert a row in tbl_status ONLY if there is **no entry** for that AssociateId for a particular department (say IT). Example:If there are 2 Associates ABC1 & ABC2. ABC1 has an entry for dept IT in tbl_stat. Then what I want is that, a row should be inserted for ABC2 in tbl_stat. – user3368855 Mar 01 '14 at 20:11
  • Okay I edited my answer slightly so this query will work for department `IT`. The only other thing you would need to specify is the value for `Sub_on` in the `SELECT` statement, since I'm not sure what kind of field it is. Otherwise, this query should be exactly what you are looking for. – jtate Mar 01 '14 at 20:16