Solved

Find records not in another table and update?

Posted on 2010-11-14
8
314 Views
Last Modified: 2012-06-21
Table1 has columns City State
Table 2 has columns City State (among others)

How can I find out all the City, State in Table1 that is not  in Table 2?

And then update Table2 with all the records (City, State)  from Table1 that aren't in Table2?


0
Comment
Question by:arthurh88
8 Comments
 
LVL 10

Expert Comment

by:hosneylk
ID: 34134024

UPDATE table1 as t1
SET col = val
WHERE   EXISTS
 (SELECT t1.*
  EXCEPT
  SELECT *
  FROM table2)

Open in new window

0
 
LVL 10

Expert Comment

by:hosneylk
ID: 34134032
or this
UPDATE table1 as t1
SET col = val
WHERE NOT EXISTS
 (SELECT 1
  FROM table2 t2 WHERE t2.city = t1.city AND t2.state = t1.state)

Open in new window

0
 
LVL 19

Accepted Solution

by:
Bhavesh Shah earned 500 total points
ID: 34134034
Hi,

For finding data,use

SELECT CITY,STATE FROM TABLE1
EXCEPT
SELECT CITY,STATE FROM TABLE2

when you say update then means you need to insert data.


INSERT INTO TABLE2
(CITY,STATE)
SELECT CITY,STATE FROM TABLE1
EXCEPT
SELECT CITY,STATE FROM TABLE2
0
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 34134036
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 34134048
This would insert the CITY and STATE that are not present in Table2 into Table2
INSERT INTO table2
SELECT CITY, STATE
FROM table1
WHERE NOT EXISTS
 (SELECT 1 
  FROM table2
  WHERE table1.CITY = table2.CITY
  AND table1.STATE = table2.STATE)

Open in new window

0
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 34134052
0
 
LVL 4

Expert Comment

by:parthmalhan
ID: 34134827
Try the following code:

insert into table2(city,state)
select city,state from table1 where city not in
(select city from table2)

this will surely help you.
0
 

Author Closing Comment

by:arthurh88
ID: 34135202
Thank you for that!  It turns out I had 0 differences

SELECT CITY,STATE FROM TABLE1
EXCEPT
SELECT CITY,STATE FROM TABLE2

so after running that, Everything was fine.  I thought perhaps I had a lot of updating, but I didn't.  THanks again for the solution!
0

Featured Post

Independent Software Vendors: 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!

Question has a verified solution.

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

Suggested Solutions

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

726 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