I need help with an Update query using tables without Unique Keys.

Hello again.  Previously you all helped me find the fields that differed. Thanks to _agx_ and mitchie.  But I need your help again.  I now need the first table to update from the records that are different in the second table.  Both tables have the exact same column names etc. my problem is I only want the rows that are different to update. But since I don't have a unique Identifier I am not sure I can do this. Example of what I want to happen:

Table1
Col1   Col2  Col3
1       123    10/29/2007
1       345    10/29/2007
1       564    10/29/2007

Table2
Col1     Col2   Col3
1      123       11/7/2007
1      543       11/7/2007
1      564       11/7/2007

Table1 after update
Col1   Col2  Col3
1       123    10/29/2007
1       546    11/7/2007
1       564    10/29/2007
The tables are linked on Col1
rcappellAsked:
Who is Participating?
 
sdstuberConnect With a Mentor Commented:
oh sorry, that's legal sql syntax in other db's.  but not Access.

try this...

DELETE FROM table1
      WHERE NOT EXISTS(SELECT 1
                         FROM table2
                        WHERE table1.col1 = table2.col1
                          AND table1.col2 = table2.col2);


INSERT INTO table1
    (SELECT col1, col2, col3
       FROM table2
      WHERE  NOT EXISTS(SELECT 1
                         FROM table1
                        WHERE table1.col1 = table2.col1
                          AND table1.col2 = table2.col2);

0
 
sdstuberCommented:
You won't  be able to do it in a single statement since you can't do 1-for-1 lookups.


first, delete all records in table1 where the col1,col2 combination not in table2,  then insert all table2 records that aren't in table1.

something like this...

delete from table1 where (col1,col2) not in (select col1,col2 from table2);

insert into table1 (select col1,col2,col3 from table2 where (col1,col2) not in (select col1,col2 from table1));



0
 
rcappellAuthor Commented:
Thank you.  Being a beginner to this would it be possible to get an example of the syntax?  Also would this all be done in one SQL query or two?  Is this similar to a find and replace function?
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
sdstuberCommented:
the syntax example is in my above reply

delete from table1 where (col1,col2) not in (select col1,col2 from table2);

insert into table1 (select col1,col2,col3 from table2 where (col1,col2) not in (select col1,col2 from table1));

no, you can't do it in one sql.  even if you had a unique key, you would still need to do multiple statements because you have no way of identifying which row in table1 would be updated with a particular row in table2.

So, it's not quite search and replace because it's not find A replace with B,  it's find A,B,C,etc   and replace with Z,Y,X,W, V, etc.  Note that table2 might give you more rows than what you're replacing in table1,  so, it's not really an update at all.
0
 
rcappellAuthor Commented:
Well I seem to be doing it incorrectly (no surprise I am a beginner at SQL) I keep getting this message from Access: You have written a subquery that can return more than one field without using the EXISTS reserved word in the main query's FROM clause. Revise the SELECT statement of the subquery to request only one field. (Error 3306)
DELETE PART, DESCRIP, REQ, CHASSIS, SEQ, ReportDate from TestA1 
WHERE (PART, ReportDate) Not In (SELECT PART, ReportDate from TestB1);

Open in new window

0
 
rcappellAuthor Commented:
Sorry but could you explain what the SELECT 1 means please.
0
 
rcappellAuthor Commented:
Worked beautifully
0
 
sdstuberCommented:
you have to select something for an exists or not exists sub query.
You could also select null

Some people write the query to return some column from the table but I don't recommend that.  Select a dummy value, it shows that the query results have no meaning in themselves.  It's an added reminder that you're only looking for the existence (or non-existence) of data, you don't actually care what that data is.
0
 
rcappellAuthor Commented:
Thank you for the explanation.  I get very confused when a line like that appears in the syntax and any and all explanations are appreciated.  Thanks again for the great code.  Sorry I didn't specify that I was using Access a little more clearly I thought the tags did that for me.  Oh well live and learn.
0
 
sdstuberCommented:
you specified fine, I'm just more used to using Oracle, so I think in terms of Oracle syntax most of the time and then forget when other db's differ.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.