Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Find records not in another table and update?

Posted on 2010-11-14
8
Medium Priority
?
318 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
[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
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 2000 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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

Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

618 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