Convert this select in a update query

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

SQL Server 2000 Forums

Transact-SQL (2000)

Convert this select in a update query

Author

Topic

CrashSerge
Starting Member

3 Posts

Posted - 2005-04-24 : 16:48:32

Hi,

I'm struguling in trying to convert this Select query into an Update query.

select
c.NoJoute,
c.EquipeLoc,
c.EquipeVis,
r.nojoute,
r.nojoueur,
r.lanceur
from
calendrier as c,
Resultat as r
where
(c.annee = 2004) and
(c.EquipeLoc = 1 or c.EquipeVis = 1) and
(r.nojoute = c.nojoute) and
(r.annee = c.annee) and
(r.equipe <> 1)

I want to do something like
Update Resultat
set resultat.lanceur = 14
where
'This is where I'm confuse...

Thanks for the help.

Serge Allard

nathans
Aged Yak Warrior

938 Posts

Posted - 2005-04-24 : 17:05:27

Read in BOL: Subqueries in UPDATE, DELETE, and INSERT Statements

Update r
set r.lanceur = 14
from resultat r ...

CrashSerge
Starting Member

3 Posts

Posted - 2005-04-24 : 17:13:43

quote:
Originally posted by nathans

Read in BOL: Subqueries in UPDATE, DELETE, and INSERT Statements

Update r
set r.lanceur = 14
from resultat r ...

Thanks a lot, it worked perfectly. I own a copy of the book SQL Server, Developer's guide, and it's not in there. All the exemple are of this type:
Update X
Set X.Y
Where bla, bla bla...

nathans
Aged Yak Warrior

938 Posts

Posted - 2005-04-24 : 17:39:16

im glad that worked out for ya... look into writing your selects with the newer syntax as well:

select c.NoJoute,
       c.EquipeLoc,
       c.EquipeVis,
       r.nojoute,
       r.nojoueur,
       r.lanceur
from calendrier c INNER JOIN Resultat r 
       ON c.nojoute = r.nojoute
...

Im guessing on the join conditions, but you get the idea :)

- Nathan Skerl

where

nathans
Aged Yak Warrior

938 Posts

Posted - 2005-04-24 : 17:57:58

a great article on filtering in the WHERE vs. JOIN...

http://www.sqlteam.com/item.asp?ItemID=11122

CrashSerge
Starting Member

3 Posts

Posted - 2005-04-24 : 20:21:01