1

http://imageshack.us/photo/my-images/839/noeuds.jpg/

Just added a link for the real table. Noeud 116 sould have INSEE_COM updated with info on 117. (116 is related as N_AMONT in 117 record)

I have this table (noueds):

NOEUD   TYPE_MAT  N_AMONT   LONG_CABLE   ADDRESS
123     REP       100       12           abc
130     AMP       229       12            
173     PPP       130        1           AAA

I would like to write an UPDATE query to fill all the data ADDRESS from node after the one that has NODES.LONG_CABLE = 1.

For example:

I searched all the nodes that have LONG_CABLE = 1, gives me noeud 173; then the node before 130 should have the same address of 173.

I can search all the nodes that will be updated with:

SELECT *
  FROM noeuds AS tab,
       [SELECT noeuds.* FROM noeuds WHERE (((noeuds.LONG_CABLE)="    1"))]. AS a1
 WHERE (((tab.NOEUD)=([a1].[n_amont])) AND ((tab.ADDRESS)="     "));

I'm using MS Access.

EDIT:

It is updateing only onrecords selected for 1m calbe. does anyone know how to make it update on the ones pointed by noeuds.n_amont not in the selected table but on the original one called noeuds?

Odones
  • 71
  • 1
  • 7
  • Why triple parentheses in `(((noeuds.LONG_CABLE)=" 1"))` when `noeuds.LONG_CABLE = " 1"` would do just as well? Why all those quotes around the value for LONG_CABLE? Surely you aren't storing pure numeric data in a CHAR(n) column, are you? The square brackets around the sub-select are aconventional. Can you not use the explicit JOIN notation instead of comma-separated expressions in the FROM clause? – Jonathan Leffler Mar 13 '12 at 20:22
  • The program that manipulate the original database do not use numeric values. When noeuds.LONG_CABLE is empty it has 5 spaces char like " ". – Odones Mar 13 '12 at 20:25
  • The square brakets the M$ ACCESS program inserted auto. – Odones Mar 13 '12 at 20:27

2 Answers2

1

I am offering this as two queries so you can see if the first one is returning the right combination of address and code to update.

FirstQ

SELECT n.NOEUD, n.ADDRESS, 
     (SELECT TOP 1 Noeud 
      FROM Noeuds WHERE Noeud<n.Noeud 
      ORDER BY Noeud DESC) AS CodeToUpdate
FROM noeuds AS n
WHERE n.LONG_CABLE="1"
ORDER BY n.NOEUD

Update

UPDATE noeuds 
INNER JOIN FirstQ 
ON noeuds.NOEUD = FirstQ.CodeToUpdate 
SET noeuds.ADDRESS = [FirstQ].[ADDRESS]</s>

EDIT re Comment

SELECT a.noeud,
       a.long_cable,
       a.address,
       noeuds_1.n_amont,
       noeuds_1.address
FROM   (SELECT *
        FROM   noeuds
        WHERE  long_cable = '1') AS a
       INNER JOIN noeuds AS noeuds_1
         ON a.n_amont = noeuds_1.noeud;

Update #2

UPDATE (SELECT n_amont,
               address
        FROM   noeuds
        WHERE  long_cable = '1') AS a
       INNER JOIN noeuds AS n
         ON a.n_amont = n.noeud
SET    n.address = a.Address
WHERE  n.address IS NULL
Fionnuala
  • 90,370
  • 7
  • 114
  • 152
  • how do you link this two? the way I do wrong gives me bad syntax. I am used to do only much simpler sql statements. – Odones Mar 13 '12 at 22:39
  • Save the first bit of SQL labelled FirstQ as a query called FirstQ, but check if it is giving you the data you expect when you run it. The second query is an update query, so make sure you have backed up your database before you test. – Fionnuala Mar 13 '12 at 22:44
  • thanks, but the nodes are not in sequential order.it is needed to search for the right node, take the a_mont node and the in the sam etable update with the address found before. – Odones Mar 13 '12 at 22:47
  • The query is asking for a noeds.noed to start.it is not working. does not change d enoeuds table. – Odones Mar 13 '12 at 22:54
  • Are you looking for something like the third query? – Fionnuala Mar 13 '12 at 23:01
  • yes, but i receive an error message with the internal select. i used SELECT * FROM noeuds AS tab, [SELECT noeuds.* FROM noeuds WHERE (((noeuds.LONG_CABLE)=" 1"))]. AS a1 WHERE (((tab.NOEUD)=([a1].[n_amont])) AND ((tab.ADDRESS)=" ")); and I got as a result all the nodes that should have the address updated (and are empty) – Odones Mar 13 '12 at 23:12
  • I'm not able to post a SS so you can see the table :( I willupload in imageshack – Odones Mar 13 '12 at 23:17
  • Your 3rd query gave me the same info from mine. Now the update one should search the .n_amont value at the .noeud record and update the record where .n_noeud = .n_amont I'm still not able to do it :( – Odones Mar 13 '12 at 23:31
  • Thank you very much, it worked fine. I had to do a small change so it could run (and add the rest of the updated fileds to complete operation). UPDATE [SELECT noeuds.* FROM noeuds WHERE (((noeuds.LONG_CABLE)=" 1"))]. AS a INNER JOIN noeuds AS n ON a.n_amont=n.noeud SET n.INSEE_COM = a.INSEE_COM, n.RIVOLI = a.RIVOLI, n.NUM_VOIE = a.NUM_VOIE WHERE ((n.INSEE_COM)=" "); – Odones Mar 14 '12 at 09:54
  • Good for you :) It is not a good idea to use an asterisk (*) to select fields. See http://stackoverflow.com/questions/321299/what-is-the-reason-not-to-use-select – Fionnuala Mar 14 '12 at 10:06
  • Hi there, I found a problem on this query that don't really match my needs. When in the forst select we select the nodes that have one node 1m away (in n.amont) it gives me a table with all that will give address for update. the problem is that when matching the nodes for update with a.n_amont = n.noeud it is not geting info from the whole table NOEUD but only with the selected table. it update only ones from the selected. It should get n_amont from selected bu update on the whole table noeud. any help on doing this? – Odones May 10 '12 at 10:02
0

Given that that

  • node no starts at 1
  • the previous node no is always = node no -1

You can do the following (not sure it works with access though):

UPDATE TableName  
SET spalteA = t2.SpalteB
FROM TableName AS t1
INNER JOIN TableName AS t2
ON t2.node = t1.node - 1
WHERE t1.node > 1
ntziolis
  • 10,091
  • 1
  • 34
  • 50
  • M$ access do not use "FROM". this one doesn't work. and the previous node is not always the one I want to update. The sequence is given by NOEUD and N_AMONT – Odones Mar 13 '12 at 20:32