UPDATE table with information from 2 other tables

Question

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.

Check this question http://stackoverflow.com/questions/1293330/how-can-i-do-an-update-statement-with-join-in-sql — jordeu, Apr 13 '12 at 10:13
@jordeu sql is a generic tag and SQL Server solutions are often not suitable for MS Access questions. — Fionnuala, Apr 13 '12 at 10:29
@Remou the answer of those question give the solution for ANSI, MySQL, SQL Server, and Oracle. — jordeu, Apr 13 '12 at 11:03

Marco · Answer 1 · 2012-04-13T10:42:58.697

1

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"

edited Apr 13 '12 at 10:42

answered Apr 13 '12 at 10:11

Marco

56,740
14
129
152

thanks for the fast reply. I'm getting syntax error on (pb.NOEUD = n.NOEUD AND (pb.TYPE_MAT = 'PBT' OR pb.TYPE_MAT = 'PBB') INNER JOIN COLINFO ci ON n.NUM_COL = ci.NUM_COL AND ci.INTEREXTER = "2" SET pb.FORFAIT = "E"). it says operator is missing. :S – Odones Apr 13 '12 at 10:20
The tag says MS Access so that will not run. For one, you need to add some parentheses on those joins. – Fionnuala Apr 13 '12 at 10:26
In M$ Access UPDATE / FROM do not work. PB is a complete table name. – Odones Apr 13 '12 at 10:29
Thanks Marco, the first attempt worked with the right parenthesis. Thanks very much for your help. This one works fine :P – Odones Apr 13 '12 at 10:45

score 1 · Accepted Answer · answered Apr 13 '12 at 10:40

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.

UPDATE table with information from 2 other tables

2 Answers2