SQL update query

[MACIT]

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

Edited October 3, 2008 by MACIT

[RabbieBurns]

The way i'd do it is do the select statements first, and then the update.

 

So something like:

 

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

[ArchersIT]

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

Edited October 3, 2008 by ArchersIT
Removed unnecessary quoting

[lightinthedark]

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:

 

 

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

[MACIT]

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:

 

 

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