Solved

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

Posted on 2007-11-26
10
239 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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 

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
 

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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MS SQL Inner Join - Multiple Join Parameters 2 32
SQL Server 2012 r2 - Make Temp Table Query Faster 5 42
SSRS 2013 - Creating a summarized report 19 35
Email Header Detail 12 55
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

770 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