Update query in SQL

[Guest]

Hi,
I'm trying to write SQL to update a Temp table from the Order table, only if
two fields are the same (PO and PartNumber). The problem is: If there is any
duplicated records in the Temp table, it only updates it once. I got the SQL
part of the query from the qbe. The sql works when I use Access' qbe, but
when I transfer it as below, it does not update duplicate rows (i.e., there
may be more than one row of data in Temp table with same PartNumber and PO).
t
It looks like there it's fairly straightforward...
here's the code I got ..


Dim db As Database
Dim strsql As String
Set db = CurrentDb

strsql = "UPDATE tblTemp "
strsql = strsql & " LEFT JOIN [Order] ON (([tblTemp].[PartNumber] =
[Order].[Part Number]) AND ([tblTemp].[PO] = [Order].[PO])) "
strsql = strsql & "SET [tblTemp].POID = [Order].[PO ID]"
db.Execute strsql

Can anyone give me any pointers as to what may go wrong?? Thank you so much!

 

[Guest]

Hi,

Change the LEFT JOIN into INNER JOIN

- Raoul

 

[Michel Walsh]

Hi,


You mean there is dup in tblTemp? They should all be updated. If there are
dup in Order, just one will be used. It is like:

Dim x As long
x=4
x=5
x=6


then, x takes only the last assignation (the last update). If you want to
merge the new records from Order, you have to use an Append query, in this
case of duplicated records from the "updating" table.



Hoping it may help,
Vanderghast, Access MVP