+ Post New Thread
Results 1 to 5 of 5
Coding Thread, SQL update query in Coding and Web Development; Wondering if anyone can help me, Im trying to construct a SQL statement and i seem to be coming unstuck ...
  1. #1
    MACIT's Avatar
    Join Date
    Sep 2008
    Posts
    51
    Thank Post
    1
    Thanked 1 Time in 1 Post
    Rep Power
    0

    SQL update query

    Wondering if anyone can help me, Im trying to construct a SQL statement and i seem to be coming unstuck so i thought of you:

    Table 1
    Pupilid, Subject, Grade

    123, Maths, A



    Table 2
    Pupilid - Subject - Grade

    123, Maths, Null


    I trying do populate the grade field in table2 from the grade field in table 1 and i struggling with the following syntax:

    update table2

    set grade = (select grade from table1 where table1.pupilid and table1.subject = table2.pupilid and table2.subject)

    where exists

    select grade from table2 where table2.pupilid and table2.subject = table1.pupilid and table1.subject)


    But SQL wont allow me to use AND statement


    Hoping someone can helpout or any guidance appreciated
    Last edited by MACIT; 3rd October 2008 at 12:21 PM.

  2. #2

    RabbieBurns's Avatar
    Join Date
    Apr 2008
    Location
    Sydney
    Posts
    5,521
    Thank Post
    1,333
    Thanked 469 Times in 306 Posts
    Blog Entries
    6
    Rep Power
    199
    The way i'd do it is do the select statements first, and then the update.

    So something like:

    Code:
    SELECT pupilID, subject, grade FROM table1
    
    UPDATE table2 SET pupilID=table1.pupilID
    UPDATE table2 SET subject=table1.subject
    UPDATE table2 SET grade=table1.grade
    I think. Ill knock it up in access and see if thats correct

  3. #3
    ArchersIT's Avatar
    Join Date
    Nov 2006
    Location
    Bedfordshire
    Posts
    114
    Thank Post
    14
    Thanked 24 Times in 20 Posts
    Rep Power
    20
    I am not sure if the query will do what you want, but you do have a syntax error here:
    select grade from table1 where table1.pupilid and table1.subject = table2.pupilid and table2.subject
    should read

    select grade from table1 where
    table1.pupilid = table2.pupilid and table1.subject = table2.subject

    Note the change in the where clause.

    So, I dont know if the whole thing will do what you want, but that should cure the syntax errors in your select statements.

    Cheers

    Jonathan
    Last edited by ArchersIT; 3rd October 2008 at 01:58 PM. Reason: Removed unnecessary quoting

  4. #4

    Join Date
    Oct 2008
    Location
    Gosport
    Posts
    64
    Thank Post
    1
    Thanked 18 Times in 13 Posts
    Rep Power
    15
    Hi,

    I know this thread is a bit old by now, but in case this is still an issue for you, here's what I would do:


    Code:
    UPDATE table1
    INNER JOIN table2
       ON table2.pupilid = table1.pupilid
      AND table2.subject = table1.subject
    SET table2.grade = table1.grade
    That will set the grades in table 2 to be the same as those in table1. It'll only update the rows that already exist in table2. If you want to update only the table2 rows which have nulls in them (so as not to over-write existing data), use a LEFT JOIN instead of the INNER one and add a "HAVING table2.code IS NULL" clause in there.

    Hope it helps someone.

    Hello
    :-Dave

  5. #5
    MACIT's Avatar
    Join Date
    Sep 2008
    Posts
    51
    Thank Post
    1
    Thanked 1 Time in 1 Post
    Rep Power
    0
    Quote Originally Posted by lightinthedark View Post
    Hi,

    I know this thread is a bit old by now, but in case this is still an issue for you, here's what I would do:


    Code:
    UPDATE table1
    INNER JOIN table2
       ON table2.pupilid = table1.pupilid
      AND table2.subject = table1.subject
    SET table2.grade = table1.grade
    That will set the grades in table 2 to be the same as those in table1. It'll only update the rows that already exist in table2. If you want to update only the table2 rows which have nulls in them (so as not to over-write existing data), use a LEFT JOIN instead of the INNER one and add a "HAVING table2.code IS NULL" clause in there.

    Hope it helps someone.

    Hello
    :-Dave
    I had already solved this with exactly that query but just wanted to say thank you for responding anyway.

    Being learning all about inner joins lately, they certainly are a useful function never had a reason to use them before.

    Thanks again

    Neil

SHARE:
+ Post New Thread

Similar Threads

  1. SQL Query Using Full Text Engine inSQL 2005
    By Phanikumar in forum Web Development
    Replies: 0
    Last Post: 22nd September 2008, 08:47 AM
  2. SQL Insert Query
    By Pashers in forum Web Development
    Replies: 1
    Last Post: 9th September 2008, 03:42 PM
  3. August update + SQL change compulsary?
    By zag in forum MIS Systems
    Replies: 8
    Last Post: 8th October 2007, 08:11 AM
  4. August Update Without SQL
    By enjay in forum MIS Systems
    Replies: 10
    Last Post: 31st August 2007, 12:58 PM
  5. SQL Query :S
    By Kained in forum Web Development
    Replies: 10
    Last Post: 25th July 2007, 02:56 AM

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

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