Update Select query? (1 Viewer)

[mafhobb]

I am trying to do an UPDATE query by using the data from a SELECT query.

I have a table called "Customer" which has several fields (ToDo, CustomerID, AddressType, CustomerName, StreetAddress,City,...). One of the fields (ToDo) may have a value of "UPDATE". I need to run a query that goes through this table and then finds all the records that have that value (UPDATE) in the ToDo field and then update all the records in the table "Address" that match the CustomerID and AddressType.

I believe that the SELECT statement to get the data would look something like this:

        "SELECT Customer.customerId, Customer.addressNr, Customer.addressType, Customer.firstname, Customer.lastname, Customer.companyName, Customer.postalcode, Customer.country, Customer.workphone, Customer.email, Customer.notes, Customer.streetaddress, Customer.city, Customer.contactTypeId " & _
        "FROM Customer " & _
        "WHERE (Customer.ToDo='UPDATE');"

And the UPDATE query would look something like this:

"UPDATE Address SET Customer.addressNr,Customer.firstname, Customer.lastname, Customer.companyName, Customer.postalcode, Customer.country, Customer.workphone, Customer.email, Customer.notes, Customer.streetaddress, Customer.city, Customer.contactTypeId" & _
        "WHERE Address.CustomerID=Customer.CustomerID AND Address.AddressType=Customer.addresstype;"

I am having trouble troubleshooting the syntax and putting the two together. Can this even be done?

mafhobb

 

[plog]

First, UPDATE queries always send alarms off in my head. You don't store calculated values in a table and if you can run code to determine which values should be changed, then that's a sort of calculation which means it shouldn't be done. You haven't really explained the purpose of the UPDATE so can you tell us why this needs to be done rather than using just a SELECT query?

Second, your UPDATE query is invalid and more than just syntactically. I can't even see what you are trying to achieve . Check out the syntax for it here: http://www.w3schools.com/sql/sql_update.asp

The UPDATE clause doesn't include the table you are actually updating, the SET clause never assigns a value to any field. So while I believe its possible to combine your 2 queries, one of them isn't valid and I can't see exactly what you are trying to do with it.

Can you explain what you want to do with your update query? Specifically what are the new values going into all those Customer fields?

 

[mafhobb]

Hello Plog,

Sorry if I did not explain this well.

I have a customer service database that works fine, but now the IT department is asking to expand its capabilities and I am very much stuck about how to do it.

They use an AS400 system and they want to be able to insert, update or delete records in the Address table in this database by providing element centric xml files exported from the AS400.

Once a day the AS400 will create a bunch of xml files with the same elements as my address table, except for an additional one (the ToDo field). This ToDo field can have three values: ADD, UPDATE, DELETE. So I have created a small database that imports these xml files into a table (Customer). This database will always be running in the server, and the code will be on a timer (also once a day)

Now the next step is to sort that data (INSERT, UPDATE, DELETE) and then act on the real Address table in the customer service database. For now I am only doing this in two tables in the same database as it is easier to troubleshoot, but in the end the tables will be in two separate databases.

With the help of some members I have already been able to setup the INSERT part of the code by using a INSERT INTO-SELECT query. It works great (Thanks!) Now I need to do the UPDATE and DELETE parts.

So what I was trying to do is do through the Select statement in the Customer table, figure out which records need to be updated and the enter the updated info into the Address table by matching CustomerID and AddressType.

This is the code that does the INSERT part

        CurrentDb.Execute "INSERT INTO Address ( customerId, addressNr, addressType, firstname, lastname, companyName, postalcode, country, workphone, email, notes, streetaddress, city, contactTypeId )" & _
        "SELECT Customer.customerId, Customer.addressNr, Customer.addressType, Customer.firstname, Customer.lastname, Customer.companyName, Customer.postalcode, Customer.country, Customer.workphone, Customer.email, Customer.notes, Customer.streetaddress, Customer.city, Customer.contactTypeId " & _
        "FROM Customer " & _
        "WHERE (Customer.ToDo='INSERT');"

I hope I did not confuse things even more..

mafhobb

 

[plog]

Good explanation and that does make sense to do your action queries. However, I don't see why VBA needs to be used at all. This could all be done via queries.

Do you have to do an UPDATE? I mean, why not delete those records and then INSERT them? That cuts it down to 2 operations:

DELETE: Delete the ones marked Delete and marked Update
INSERT: Insert the ones marked Insert and marked Update

 

[mafhobb]

That's a good idea. In the meantime I have been tinkering with the following and it seems to do what I was hoping for:

UPDATE Customer INNER JOIN Address ON (Customer.addressType = Address.addressType) AND (Customer.customerId = Address.customerId) SET Address.addressNr = [Customer].[addressNr], Address.firstname = [Customer].[firstname], Address.lastname = [Customer].[lastname], Address.companyName = [Customer].[companyName], Address.postalcode = [Customer].[postalcode], Address.country = [Customer].[country], Address.workphone = [Customer].[workphone], Address.email = [Customer].[email], Address.notes = [Customer].[notes], Address.streetaddress = [Customer].[streetaddress], Address.city = [Customer].[city], Address.contactTypeId = [Customer].[contactTypeId]
WHERE (((Customer.ToDo)="Update"));

Do you see anything obviously wrong?

mafhobb

 

[plog]

The table in your UPDATE clause is Customer, but in the SET clause you are changing the values of Address fields. The table in your UPDATE clause needs to be on the left side of the equals sign for all parts of your SET clause.

 

[mafhobb]

It seems to do exactly what I needed...

The values to change are in the Address fields but the criteria originator is in the Customer table. It's an inner joint query.

mafbus