?
Solved

Find records not in another table and update?

Posted on 2010-11-14
8
Medium Priority
?
316 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
Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

 
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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Suggested Courses

800 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