CODEWITHSUNDEEP
sqlms-access
Odones
Odones

Reputation: 71

UPDATE table with information from 2 other tables

I have 3 tables with info as follows:

NOEUDS:

NOEUD   TYPE_MAT   NUM_COL
1        PBT         100
2        AMP         
3        REP    
4        PBP         120

COLINFO:

NUM_COL    INTEREXTER
100            1
120            2

PB:

NOEUD     FORFAIT
1           I
3            
4           E

I would like to update table PB.forfait with an E when in colinfo.num_col = 2 for example.

I'm trying something like this, but still did not manage to succeed. It is a Microsoft Access database.

UPDATE pb

inner join (

SELECT noeud, type_mat, n.num_col, c.interexter
FROM noeuds AS n, colinfo AS c
WHERE ((NOEUDS.TYPE_MAT="PBT") Or (NOEUDS.TYPE_MAT="PBP")) 
  And (n.num_col=c.num_col)

) n on pb.noeud=n.noeud

SET (PB.FORFAIT = "E")

WHERE (n.INTEREXTER="2");

Thanks in advance.

Upvotes: 0

Views: 322

Answers (2)

Marco
Marco

Reputation: 57573

Try this

UPDATE 
(NOEUDS AS n INNER JOIN COLINFO AS ci ON n.NUM_COL = ci.NUM_COL) 
INNER JOIN PB ON n.NOEUD = PB.NOEUD 
SET PB.FORFAIT = "E"
WHERE (n.TYPE_MAT="PBP" Or n.TYPE_MAT="PBT") 
  AND ci.INTEREXTER=2

If ci.INTEREXTER is a string then use

AND ci.INTEREXTER = "2"

Upvotes: 1

Fionnuala
Fionnuala

Reputation: 91316

How about:

UPDATE (PB 
INNER JOIN Noeuds ON PB.NOEUD = Noeuds.NOEUD) 
INNER JOIN ColInfo ON Noeuds.NUM_COL = ColInfo.NUM_COL 
SET PB.FORFAIT = "E"
WHERE (((ColInfo.INTEREXTER)=2));

I built this using the query design window, a very useful tool in MS Access.

Upvotes: 1

Related Questions