Solved

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

Posted on 2007-11-26
10
238 Views
Last Modified: 2010-05-18
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
0
Comment
Question by:rcappell
  • 5
  • 5
10 Comments
 
LVL 73

Expert Comment

by:sdstuber
ID: 20351429
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
 

Author Comment

by:rcappell
ID: 20351603
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
 
LVL 73

Expert Comment

by:sdstuber
ID: 20351641
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
 

Author Comment

by:rcappell
ID: 20351866
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
 
LVL 73

Accepted Solution

by:
sdstuber earned 125 total points
ID: 20352265
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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 

Author Comment

by:rcappell
ID: 20352588
Sorry but could you explain what the SELECT 1 means please.
0
 

Author Closing Comment

by:rcappell
ID: 31411012
Worked beautifully
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 20353110
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
 

Author Comment

by:rcappell
ID: 20353601
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
 
LVL 73

Expert Comment

by:sdstuber
ID: 20353642
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

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
MySQL left join performance 4 30
Using OPENQUERY in a SELECT statement 6 36
Get Duration of last Status Update 4 30
t-sql split string into multiple rows 7 35
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now