Solved

Find records not in another table and update?

Posted on 2010-11-14
8
310 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
Comment Utility

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
Comment Utility
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
Comment Utility
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
 
LVL 19

Expert Comment

by:Bhavesh Shah
Comment Utility
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 57

Expert Comment

by:Raja Jegan R
Comment Utility
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
Comment Utility
0
 
LVL 4

Expert Comment

by:parthmalhan
Comment Utility
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
Comment Utility
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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Suggested Solutions

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

743 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now