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 ...
3rd October 2008, 12:16 PM #1
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:
Pupilid, Subject, Grade
123, Maths, A
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:
set grade = (select grade from table1 where table1.pupilid and table1.subject = table2.pupilid and table2.subject)
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.
3rd October 2008, 12:42 PM #2
The way i'd do it is do the select statements first, and then the update.
So something like:
I think. Ill knock it up in access and see if thats correct
SELECT pupilID, subject, grade FROM table1
UPDATE table2 SET pupilID=table1.pupilID
UPDATE table2 SET subject=table1.subject
UPDATE table2 SET grade=table1.grade
3rd October 2008, 01:58 PM #3
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
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.
Last edited by ArchersIT; 3rd October 2008 at 01:58 PM.
Reason: Removed unnecessary quoting
27th October 2008, 09:03 AM #4
- Rep Power
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:
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.
INNER JOIN table2
ON table2.pupilid = table1.pupilid
AND table2.subject = table1.subject
SET table2.grade = table1.grade
Hope it helps someone.
27th October 2008, 08:20 PM #5
I had already solved this with exactly that query but just wanted to say thank you for responding anyway.
Originally Posted by lightinthedark
Being learning all about inner joins lately, they certainly are a useful function never had a reason to use them before.
By Phanikumar in forum Web Development
Last Post: 22nd September 2008, 08:47 AM
By Pashers in forum Web Development
Last Post: 9th September 2008, 03:42 PM
By zag in forum MIS Systems
Last Post: 8th October 2007, 08:11 AM
By enjay in forum MIS Systems
Last Post: 31st August 2007, 12:58 PM
By Kained in forum Web Development
Last Post: 25th July 2007, 02:56 AM
Users Browsing this Thread
There are currently 1 users browsing this thread. (0 members and 1 guests)