Results 1 to 12 of 12
  1. #1
    Join Date
    Feb 2004
    Posts
    47

    Unanswered: Looping in update queries

    Hi,

    How does one do looping in update queries (MSAccess 2000- SQL view)?

    Why does something like this not work?

    While i < 10
    Begin
    SELECT "Column1" FROM "TableName";
    INSERT INTO "TableName" ("Column1") VALUES ("FirstValue");
    End

    Can someone please help with the correct code?

    Regards

  2. #2
    Join Date
    Aug 2003
    Posts
    42
    what areyou trying to do? Your code looks like your running the same query 10 times

  3. #3
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    insert into tableB
    select TOP 10 column
    from tableA
    order by orderColumn
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  4. #4
    Join Date
    Feb 2004
    Posts
    47
    Hi r, Thanks Mate! Your code works!!

    Here is what I am trying to do:

    TableC is the bridging table in a many-to-many relationship and with ColumnA and ColumnB holding the IDs of TableA and TableB respectively. TableC has some data for combination of TableA and TableB.

    I would like to update ColumnA of TableC with all IDs of TableA and ColumnB of TableC with all IDs of TableB.
    ...to ensure that I have all the possible combinations in TableC.

    1. How can I do this using an INSERT INTO query?
    2. Do you have any SQL reference URL to get the nice commands like TOP 10 and syntax?
    3. In an MSAccess 2000 query can I have only one SQL statement?

    Wader.... hope this answers ur question too...

    Cheers

  5. #5
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    insert into tableC
    select ta.ID, tb.ID
    from tableA ta
    INNER JOIN
    tableB tb ON
    1 = 1
    LEFT OUTER JOIN
    tableC tc ON
    ta.id = c.taID AND
    tb.id = c.tbID
    WHERE c.taID IS NULL;
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  6. #6
    Join Date
    Feb 2004
    Posts
    47
    Hi Guru,

    Tried this....

    INSERT INTO TblRelations
    SELECT TblStudents.StudentID, TblTeachers.TeacherID
    FROM TblStudents StudentID
    INNER JOIN
    TblTeachers TeacherID ON
    1 = 1
    LEFT OUTER JOIN
    TblRelations RelationID ON
    TblStudents.StudentID = TblRelations.StudentID AND
    TblTeachers.TeacherID = TblRelations.TeacherID
    WHERE TblRelations.StudentID IS NULL;

    But I get the error...

    Syntax Error (missing operator) in query expression '1 = 1
    LEFT OUTER JOIN
    TblRelations RelationID ON
    TblStudents.StudentID = TblRelations.StudentID

  7. #7
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    INSERT INTO TblRelations
    SELECT TblStudents.StudentID, TblTeachers.TeacherID
    FROM (TblStudents
    INNER JOIN
    TblTeachers ON
    1 = 1)
    LEFT OUTER JOIN
    TblRelations ON
    TblStudents.StudentID = TblRelations.StudentID AND
    TblTeachers.TeacherID = TblRelations.TeacherID
    WHERE TblRelations.StudentID IS NULL;
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  8. #8
    Join Date
    Feb 2004
    Posts
    47
    Gives Error....

    Join Expression not supported.

  9. #9
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    INSERT INTO TblRelations
    SELECT V.StudentID, V.TeacherID
    FROM
    (Select S.StudentID, T.TeacherID
    from
    TblStudents S, TblTeachers T) V
    LEFT OUTER JOIN
    TblRelations R ON
    V.StudentID = R.StudentID AND
    V.TeacherID = R.TeacherID
    WHERE R.StudentID IS NULL;
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  10. #10
    Join Date
    Feb 2004
    Posts
    47
    This works!! You r a genius!!! But pray... what is V., T. and S.?

    Rgds



    Originally posted by r123456
    INSERT INTO TblRelations
    SELECT V.StudentID, V.TeacherID
    FROM
    (Select S.StudentID, T.TeacherID
    from
    TblStudents S, TblTeachers T) V
    LEFT OUTER JOIN
    TblRelations R ON
    V.StudentID = R.StudentID AND
    V.TeacherID = R.TeacherID
    WHERE R.StudentID IS NULL;

  11. #11
    Join Date
    Feb 2004
    Posts
    47
    Hi,

    There are two other tables, Months and Years with MonthsID and YearsID as the primary key.

    The TblRelations need to have all Months and Years combination along with Teachers and Students for tracking their progress by month.

    Would it be possible with one query.

    Regards,
    Kashi.

  12. #12
    Join Date
    Feb 2004
    Posts
    47
    Hi,

    I got the way to do it....

    Here is the code...

    INSERT INTO TblRelations ( MonthsID, StudentID, TeacherID, YearsID )
    SELECT TblMonths.MonthsID, TblStudents.StudentID, TblTeachers.TeacherID, TblYears.YearsID
    FROM TblMonths, TblStudents, TblTeachers, TblYears;

    I am told that this is called an "Unrelated Append" code.

    Rgds.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •