[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

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

Posted on 2007-11-26
10
Medium Priority
?
247 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…

650 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