If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Update Query
I'm trying to setup a seemingly easy function but can't seem to find the
solution. I have two tables with the same structure except the 2nd table has an extra field. How can I insert that field into the 1st table when a match occurs between both tables? Thanks J |
#2
|
|||
|
|||
Update Query
J -
If the first table doesn't have the field in its structure, then you must add the field to the table before you can update the data. Assuming both tables have the same structure, and the second table has some data in a field that is null in the first table, then you can use an update query like this: UPDATE table1, table2 SET table1.fieldname = table2.fieldname WHERE table1.fieldname is null AND table2.primarykey is not null AND table1.primarykey = table2.primarykey You will of course need to use your real table and field names, making sure to inner join on the field(s) that uniquely identify each record. -- Daryl S "jskay" wrote: I'm trying to setup a seemingly easy function but can't seem to find the solution. I have two tables with the same structure except the 2nd table has an extra field. How can I insert that field into the 1st table when a match occurs between both tables? Thanks J . |
#3
|
|||
|
|||
Update Query
"jskay" u57998@uwe wrote in message news:a323b9a853cfa@uwe...
I'm trying to setup a seemingly easy function but can't seem to find the solution. I have two tables with the same structure except the 2nd table has an extra field. How can I insert that field into the 1st table when a match occurs between both tables? Do you really mean that the 2nd table has an extra field in its design? That is: Table1 --------- Field1 Field2 Field3 Table2 --------- Field1 Field2 Field3 Field4 ? Or do you mean that the tables have the same structure and number of fields, but the second table has data in one field that the first table does not? You can't selectively add a field to a table; the field exists for all records or not at all. But you can update that field for some records. I have to think that that is what you want to do. So for example, both tables may have fields Field1, Field2, Field3, and Field4, and you may want to update those records in Table1 that have the same value in Field1 as a record in Table2, setting Field4 in Table1. If that's what you're trying to do, the SQL of such a query would be like this: UPDATE Table1 INNER JOIN Table2 ON Table1.Field1 = Table2.Field1 SET Table1.Field4 = Table2.Field4 -- Dirk Goldgar, MS Access MVP Access tips: www.datagnostics.com/tips.html (please reply to the newsgroup) |
Thread Tools | |
Display Modes | |
|
|