update + incrémenter compteur

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 2005 Forums

Transact-SQL (2005)

update + incrémenter compteur

Author

Topic

elkhanssaa
Starting Member

5 Posts

Posted - 2008-05-09 : 07:24:56

je voulais une requette ou une procedure qui calcule en faisant un update pour faire la chose suivante (sqlserver2005):

date objet leg
12/02/2002 X leg1
12/02/2002 X leg2
12/02/2002 X leg3
12/02/2002 Y leg1
12/02/2002 Y leg2

Merci

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-09 : 07:29:49

UPDATE f
SET f.Leg = 'Leg' + f.RecID
FROM (
SELECT Leg, ROW_NUMBER() OVER (PARTITION BY Date, Objet ORDER BY Date) AS RecID
) AS f

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

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-05-09 : 07:30:30

Google gave me this translation:

quote:
I wanted a requette or a procedure by which calculates an update to the next thing

Please post expected output and your request in plain english please.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-05-09 : 07:31:35

quote:
Originally posted by Peso

UPDATE f
SET f.Leg = 'Leg' + f.RecID
FROM (
SELECT Leg, ROW_NUMBER() OVER (PARTITION BY Date, Objet ORDER BY Date) AS RecID
) AS f

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

So, you have cross-language mind reading capabilities as well, Peter?
Cool!

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-09 : 07:33:29

Je parle francais...
Requette is misspelled, should say requete and means "query".

"I want a query or a procedure which updates to do the following thing" would be a fairly proper translation.

Und ich spreche Deutsches auch!

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

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-05-09 : 07:42:31

quote:
Originally posted by Peso

Je parle francais...
Requette is misspelled, should say requete and means "query".

"I want a query or a procedure which updates to do the following thing" would be a fairly proper translation.

Und ich spreche Deutsches auch!

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

Das ist interessant!

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

elkhanssaa
Starting Member

5 Posts

Posted - 2008-05-09 : 07:44:27

Merci pour votre réponse
La table est fact:
Date :scheduled_departure
objet :plane_sk

mais il me donne des erreurs de compilation
Msg 207, Niveau 16, État 1, Ligne 4
Nom de colonne non valide : 'leg'.
Msg 207, Niveau 16, État 1, Ligne 4
Nom de colonne non valide : 'Scheduled_Departure'.
Msg 207, Niveau 16, État 1, Ligne 4
Nom de colonne non valide : 'Plane_sk'.
Msg 207, Niveau 16, État 1, Ligne 4
Nom de colonne non valide : 'Scheduled_Departure'.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-09 : 07:46:00

My mistake.
Try this


UPDATE f
SET f.Leg = 'Leg' + f.RecID
FROM (
SELECT Leg, ROW_NUMBER() OVER (PARTITION BY Date, Objet ORDER BY Date) AS RecID
FROM YourOriginalTableNameHere
) AS f

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-09 : 07:47:31

Si vous voulez l'aide appropriee, vous devriez signaler votre definition de table de sorte que nous puissions voir que quelle colonne existe et quelle colonnes pas existe.

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

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-05-09 : 07:47:43

quote:
Originally posted by Peso

My mistake.
Try this

UPDATE f
SET f.Leg = 'Leg' + CAST(f.RecID AS VARCHAR(10))
FROM (
SELECT Leg, ROW_NUMBER() OVER (PARTITION BY Date, Objet ORDER BY Date) AS RecID
FROM YourOriginalTableNameHere
) AS f

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

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

elkhanssaa
Starting Member

5 Posts

Posted - 2008-05-09 : 08:06:16

merci bcp
en fait ça marchait avec :
UPDATE f
SET f.Leg =f.RecID
FROM (
SELECT Leg, ROW_NUMBER() OVER (partition by scheduled_departure order by scheduled_departure,plane_sk) AS RecID
FROM fact
) AS f

mais il trie pa les date
en executant ta requette initiale il donne

12/05/2009 00:00:00 222 2 1
11/05/2009 00:00:00 88 1 22
11/05/2009 00:00:00 88 2 77
19/08/2006 00:00:00 44 1 44
11/05/2009 00:00:00 222 1 777
12/05/2009 00:00:00 222 1 66
NULL NULL NULL NULL

elkhanssaa
Starting Member

5 Posts

Posted - 2008-05-09 : 08:11:47

En fait stp comment on peut ajouter une condition :scheduled_departure>=11/05/2009

merci bcp

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-09 : 08:13:07

Which columns do you want the Leg number to be partitioned by?
In you initial example I thought it was obvious that Date should be included.
You have to specify your business rules more clearly.

Scheduled_Departure was not even mentioned in your orignal post.

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

elkhanssaa
Starting Member

5 Posts

Posted - 2008-05-09 : 08:23:22

en fait je voulais pour chaque date(departure scheduled) et plane_sk(objet) un leg qui s'incrémente et par ordre croissant de date

exp:
12/02/2002 A leg1
12/2/2002 A leg2
13/02/2002 A leg1
14/02/2002 B leg1
14/02/2002 B leg2
14/02/2002 X leg1

avec 1 condition date> 13/02/2002(par exemple)

Merci

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources