?
Solved

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

Posted on 2007-11-26
10
Medium Priority
?
244 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
10 Comments
 
LVL 74

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 74

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
How to Create Failover DNS Record Sets in Route 53

Route 53 has the ability to easily configure DNS record sets specifically for failover scenarios. These failover record sets can be configured to failover to full-blown deployments in other regions or to a static HTML page that informs your customers of the issue.

 

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 74

Accepted Solution

by:
sdstuber earned 375 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
 

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 74

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 74

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

Enroll in August's Course of the Month

August's CompTIA IT Fundamentals course includes 19 hours of basic computer principle modules and prepares you for the certification exam. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses
Course of the Month12 days, 19 hours left to enroll

777 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