Update from a select - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Update from a select

Author

Topic

lucsky8
Posting Yak Master

105 Posts

Posted - 2008-07-09 : 13:31:52

Hi i have 2 seperate table they both have a student number except the student number in tblMath5 is empty i need to take the one froom tblEleves.

First table
tblMath5

Second table
tblEleves

I need to take the student number from tblEleves and ypdate it in tblMath5

I try somthing like this but it not woking!

update tblMath5 set tblMath5.strNumeroEtudiant=
(select tblEleves.intMatricule from tblMath5,tblEleves
where tblMath5.strNom = tblEleves.strNom and tblMath5.strPreNom = tblEleves.strPreNom and tblMath5.intEcoleId = tblEleves.intEcoleId)

Any idea tks in advance

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-09 : 13:34:14

Why is it not working?

1) Do you get an error?
2) Do you get same value for all records?
3) Do you get invalid values for all or only some records?

E 12°55'05.25"
N 56°04'39.16"

lucsky8
Posting Yak Master

105 Posts

Posted - 2008-07-09 : 13:35:24

hi thk for your reply
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-09 : 13:41:01

The error says you have more than one student in a schoole with same forename and lastname.
Try this

update		x
set		x.strNumeroEtudiant = e.intMatricule
from		 tblMath5 as x
left join	tblEleves as e on e.strNom = x.strNom
			and e.strPreNom = x.strPreNom
			and e.intEcoleId = x.intEcoleId

E 12°55'05.25"
N 56°04'39.16"

lucsky8
Posting Yak Master

105 Posts

Posted - 2008-07-09 : 13:43:20

Hi tks for your help it work!!!

quote:
Originally posted by Peso

The error says you have more than one student in a schoole with same forename and lastname.
Try this
update x
set x.strNumeroEtudiant = e.intMatricule
from tblMath5 as x
left join tblEleves as e on e.strNom = x.strNom
and e.strPreNom = x.strPreNom
and e.intEcoleId = x.intEcoleId

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-09 : 13:46:16

Yes, it works, but you should remedy the underlying problem.

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources