Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 322
  • Last Modified:

Find records not in another table and update?

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
arthurh88
Asked:
arthurh88
1 Solution
 
hosneylkCommented:

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

Open in new window

0
 
hosneylkCommented:
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
 
Bhavesh ShahLead AnalysistCommented:
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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
Bhavesh ShahLead AnalysistCommented:
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
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
 
Bhavesh ShahLead AnalysistCommented:
0
 
parthmalhanCommented:
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
 
arthurh88Author Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now