Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

This question pertains to a MySQL database, although, in the end, this may be database neutral.

I am trying to populate an empty table (let's call it NewTable) that I created. The data population is based upon retrieving a bunch of data from other tables, along with occasional checking of that data to create a given value. The crux is that I want to get this data for every person found in a given table, let's call it table A. And there are conditionals involved too as I retrieve some of this data. Here is some pseudocode for it….

For each personID in TableA
    select statistic from a column in TableA for that personID as column 1 of NewTable
    select statistic from a column in TableB for that personID as column 2 of NewTable
    select statistic from a column in TableC based upon a value for that personID found in another TableD
          If selected statistic has value X, then assign column 3 of NewTable a certain value; otherwise assign column 3 of NewTable another value
    :
    :
End Loop after data retrieved for last personID in TableA

NewTable should now be populated after this loop ends.

Here is a bit more specific information based on the comment below:

**TableA:**

PersonID : char(6)
Points : Decimal(3,1)

**TableB:**

PersonID : char(6)
Points : Decimal(3,1)

**TableC:**

UID : tinyint(6)
Seas : int(10000)
Wk : tinyint(20)
V : char(3)
H : char(3)

**TableD:**

PersonID : char(6)
Grp : char(3)

**NewTable:**

PersonID: char(6)
Points : Decimal(3,1)
Points_Alt : Decimal(3,1)
Pattern : tinyint(6)


For each PersonID AS current in TableA:
   SET current AS PersonID IN NewTable
   SELECT Points FROM TableA WHERE PersonID = current AS Points IN NewTable
   SELECT Points FROM TableB WHERE PersonID = current AS Points_Alt IN NewTable
   For each UID in TableC WHERE Seas = 2011 AND Wk = 10:
        if V == SELECT Grp FROM TableD WHERE TableA.PersonID = TableD.PersonID Then
               SET 3 AS Pattern IN NewTable
        else if H = select Grp FROM TableD WHERE TableA.PersonID = TableD.PersonID Then
               SET 6 AS Pattern IN NewTable
    End Loop
End Loop

Can anyone provide me an example of how to do this using a SQL query? Some actual example SQL code would be very helpful.

Thank you.

share|improve this question
    
The actual table structure along with actual rules would help us helo you. Pseudo code is fine for discussion of an idea but real answers generally require specific data structures. –  Max Vernon Oct 23 '12 at 3:52
1  
I added some more specifics to my original post. I hope this helps a bit more. Thanks. –  Joel Marcey Oct 23 '12 at 4:40
    
In the case of your original header-comment, if it were too basic we would just migrate to SO. As it is, I think this Q is fine here. The other moderators and I agree this question is fine here. Therefore, you're ok. –  jcolebrand Oct 23 '12 at 14:36

1 Answer 1

up vote 1 down vote accepted

I might be misunderstanding your question; however this should work. (The code is written for SQL Server since I don't have MySQL experience.)

CREATE TABLE TableA (PersonID INT, Points decimal(10,2));
CREATE TABLE TableB (PersonID INT, Points decimal(10,2));
CREATE TABLE TableC (UID INT, Seas INT, Wk INT, V char(3), H char(3));
CREATE TABLE TableD (PersonID INT, Grp char(3));
CREATE TABLE NewTable (PersonID INT, Points decimal(10,2), 
        Points_Alt decimal(10,2), Pattern int);

INSERT INTO TableA VALUES (1, 10);
INSERT INTO TableB VALUES (1, 23);
INSERT INTO TableA VALUES (2, 11);
INSERT INTO TableB VALUES (2, 24);
INSERT INTO TableC VALUES (3, 2011, 10, 3, 4)
INSERT INTO TableC VALUES (4, 2011, 10, 5, 6)
INSERT INTO TableD VALUES (1, 3);
INSERT INTO TableD VALUES (2, 6);

INSERT INTO NewTable (PersonID, Points, Points_Alt)
SELECT TableA.PersonID, TableA.Points, TableB.Points AS Points_Alt
FROM TableA
    INNER JOIN TableB ON TableA.PersonID = TableB.PersonID;

UPDATE NewTable 
SET Pattern = 3
FROM NewTable INNER JOIN TableD ON NewTable.PersonID = TableD.PersonID
    , TableC
WHERE TableC.Seas = 2011 
    AND TableC.Wk = 10
    AND TableC.V = TableD.Grp;

UPDATE NewTable 
SET Pattern = 6
FROM NewTable INNER JOIN TableD ON NewTable.PersonID = TableD.PersonID
    , TableC
WHERE TableC.Seas = 2011 
    AND TableC.Wk = 10
    AND TableC.H = TableD.Grp;

SELECT * FROM NewTable

The first statement creates the data in NewTable and the 2 remaining statements set Pattern to the particular value based on either H or V as in your pseudo-code.

share|improve this answer
1  
Max, thank you very much. So the UPDATE serves as an implicit IF statement and I guess there is an implicit FOR as well within the INSERT and UPDATE to go through all of the PersonIDs. I had to modify the UPDATE syntax a bit since UPDATE for MySQL does not accept FROM, I don't think. It looks like this now: UPDATE NewTable INNER JOIN TableD ON NewTable.PersonID = TableD.PersonID, TableC SET Pattern = 3 WHERE TableC.Seas = 2011 AND TableC.Wk = 10 AND TableC.V = TableD.Grp; –  Joel Marcey Oct 23 '12 at 15:45
1  
That syntax is so much nicer. I wish SQL Server did not require the FROM clause for UPDATE statements! –  Max Vernon Oct 23 '12 at 16:58

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.