473,326 Members | 2,588 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

SQL expert needed - update TableA from TableB in access

Ray
Hello,

This one I think should be easy when you now how but I couldn't get it
today.
I have to run a kind of double check routine.
1 database, 2 tables. No linking or referential integrity or any of that.
TableA has 2 fields that matter - [CoilNumber] [MaterialType].... [and
others]
TableB is exactly the same - [CoilNumber] [MaterialType].... [and
others]

In TableA both fields have values
In TableB only [CoilNumber] has a value.

I need two things to happen here, and here is the kind of pseudo code I'm
working from to try and explain it.
UPDATE
TableB[MaterialType] = TableA[MaterialType]
WHERE
TableB[MaterialType] = "" AND TableB[CoilNumber] =
TableA[CoilNumber]

Stage 1: Make the two tables the same
Stage 2: If the the field in TableB already has a value then pass up the
value and throw an error
Don't worry about why it is this way, and the talk about data duplication
etc. I'm just doing what I'm told.

Any help appreciated.

Ray.
Jul 17 '05 #1
4 4537
This is the SQL you can use in a query:

UPDATE TableB INNER JOIN TableA ON [TableB].CoilNumber =
TableA.CoilNumber SET [TableB].MaterialType = [TableA].[MaterialType]

That's all... Good Luck!
Jul 17 '05 #2
Ray
Where is the bit where we only update rows in TableB that = "" ?
Or will the UPDATE only affect rows with differences?
"Stanislav" <Be***@abv.bg> wrote in message
news:38**************************@posting.google.c om...
This is the SQL you can use in a query:

UPDATE TableB INNER JOIN TableA ON [TableB].CoilNumber =
TableA.CoilNumber SET [TableB].MaterialType = [TableA].[MaterialType]

That's all... Good Luck!

Jul 17 '05 #3
for stage 2: get a list of all numbers where the materialtype is not
null.
SELECT Table2.CoilNumber, Table2.MaterialType
FROM Table2
WHERE (((Table2.MaterialType) Is Not Null));

for stage 1:
make an update query like:
UPDATE Table1 INNER JOIN Table2 ON Table1.CoilNumber =
Table2.CoilNumber SET Table2.MaterialType = [Table1].[materialtype];

If you want to make that more safe then update only the fields in
table2 where the marialtype is null:

UPDATE Table1 INNER JOIN Table2 ON Table1.CoilNumber =
Table2.CoilNumber SET Table2.MaterialType = [Table1].[materialtype]
WHERE (((Table2.MaterialType) Is Null));
Jul 17 '05 #4
Ray
Thank you for the help. I did get a grip on the UPDATE command but in the
end it seems to have been unsuitable.
I needed to update tableB from A but if there was an entry already in B I
needed to catch that error so I just did it on
the VB end instead.

Thanks again for the time.

Ray.
Jul 17 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Google Mike | last post by:
I have one table of new records (tableA) that may already exist in tableB. I want to insert these records into tableB with insert if they don't already exist, or update any existing ones with new...
2
by: beena | last post by:
Apologize for posting this question.... Yes there were postings on update with join.... My question involves 4 table join... (hopefully qualifies as a new question) Need to convert the...
9
by: Deja User | last post by:
This is probably very simple but I can't figure out a way to update one table from another table. Here is an example: ------ TABLEA ------ first_name last_name
7
by: gthompson | last post by:
Is this possible: Read fields(rows/columns) from one sql database table (TableA). Then edit/update the same 'field' in TableA; and in TableB edit/update a different field - all at the same time?...
1
by: craig.keightley | last post by:
I have the following table tableA column_a column_x column_y column_z tableB column_x column_y
1
by: G Gerard | last post by:
Hello I am trying to update a table (TableB) using records from a second table (TableA)
5
by: Sphenix | last post by:
------------------------ UPDATE A SET A.ID = '?' + A.ID FROM TABLEA A LEFT OUTER JOIN TABLEB B ON A.INDEX = B.INDEX WHERE B.DUP_ID IS NULL ------------------------ seems like update with...
1
by: DrSwens | last post by:
Using MS Access 2002/XP I am trying to create an update query which will count the number of times a particular item has been referenced in another table and store that value in the first table. For...
2
by: jjb1214 | last post by:
Hi all, I've been unable to find the answer I'm looking for on the forums, so I'm asking my first-ever question. I'm building a windows forms application in Visual Basic using Visual Studio 2008....
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.