0

Following the information and help from this one SQL Update info using other record from the same table

I noticed it is not doing the job properly.

The problem is:

It is joining the table NOEUD with table created from the sub-query inside inner join. It updates only the noeud from the sub-query table and not the all nodes from the noeud table.

The selection is made properly on the sub-query (list all the fields that has noeud.n_amont = noeud.noeud with 1m cable distance) because n_amont is the previous noeud. I want to update all the n_amont from the selection which matches the correspondent noeud from the whole table noeud.

Thanks in advance.

UPDATE [SELECT noeuds.* FROM noeuds WHERE (((noeuds.LONG_CABLE)="    1") AND (noeuds.DELETED=" "))]. 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)="     ");

Can anyone help?

EDIT:

I have this table (noueds):

NOEUD   TYPE_MAT  N_AMONT   LONG_CABLE   RIVOLI (+3 fields for update)
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 rivoli of 173.

This cable gives me the address of this equipment installed on certain streets. When I fill the PPP (type) it do not translate the address to the one at the same pole or manhole (1 m distant = connector).

With this query I posted I can select the nodes that has a previous one (173) but when joining it only joins the n_amont 130 (node related 130), if it is 1m distant from other equip. in this case it is ignored and the address is not updated. The query sould run on table NOEUDS and then find the equips that needs updates.

Any more sugestion on fixing this?

Community
  • 1
  • 1
Odones
  • 71
  • 1
  • 7
  • Ok, so in your example above, should NOEUD#130 have the same Rivoli as NOEUD#173? #173's cable is 1m, but 130's is 12m. If no, should it if 130's cable length was 1m? – ristonj May 14 '12 at 17:34
  • NOEUD#130 should have the same address as #173. This table means that equipment 173 is connected using 1m cable from the Noeud #130, so it needs the same address. but node 3130 is away from other one with other address 12m away. – Odones May 15 '12 at 09:27

1 Answers1

1

Try this instead: 

UPDATE noeuds INNER JOIN 
(SELECT noeuds.* FROM noeuds WHERE (((noeuds.LONG_CABLE)=" 1") AND (noeuds.DELETED=" "))) a ON noeuds.n_amount = a.n_amount
SET noeuds.insee_com = a.insee_com, noeuds.rivoli = a.rivoli, noeuds.num_voie = a.num_voie
ristonj
  • 1,590
  • 1
  • 12
  • 15
  • Thanks for your help, but syntaxe don't let use "update ... from" – Odones May 14 '12 at 13:39
  • I had the same result as before. It is matching the selection of thematerial that is distantr 1m long in cable list to the same list with noeud = n_amont, should match n_amont with the original table. All the material that is away n meters from the next on is not listed but is not being collect so we can paste the same address. – Odones May 14 '12 at 14:55
  • Ok, I changed it again. Is this what you're looking for? If not, can you please explain exactly what you're trying to do? – ristonj May 14 '12 at 15:41
  • nop. it is not good in this way. I wil explain with more detail the table and the current results. – Odones May 14 '12 at 16:35
  • UPDATE [SELECT noeuds.* FROM noeuds WHERE (((noeuds.LONG_CABLE)=" 1") AND (noeuds.DELETED=" "))]. 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.TYPE_MAT) <> "PBP") AND ((N.TYPE_MAT) <> "PBT")); I had a problem with the WHERE clause. Now seems to work good this way. The problems were because the lack of default introduction by the program I use. Sometime suses spaces, some "". – Odones May 25 '12 at 08:32