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

x
?
Solved

MS SQL - Need to update a column in one table based off of a left outer join

Posted on 2014-12-05
2
Medium Priority
?
117 Views
Last Modified: 2014-12-05
Below is a query I run to find data from two tables.   There is a nchar(10) column in the IMFiledata table called "imported" that I want to update with "Yes" when the query finds records that match but I've been unsuccessful in working an update clause into the query.  

select A.Custbox, A.tempid, A.DEPT, A.boxtype,
       B.CLIENT_NBR, B.MATTER_DESC, B.CLIENT_NAME, B.LAWYER_1, B.MATTER_NBR, B.CLOSE_DATE
from NDXData A
left outer join IMFiledata B on A.CustBox =B.skpboxnbr
where A.Custbox <> '' and b.FILE_STATUS <>'OUT' and A.DEPT <>''

Any ideas?

Thanks.
0
Comment
Question by:RavenTim
[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
2 Comments
 
LVL 10

Accepted Solution

by:
Walter Padrón earned 2000 total points
ID: 40483500
If you use an left outer join you includes the non-matching rows too

UPDATE IMFiledata 
SET imported= 'YES'
FROM 
(
    select A.Custbox, A.tempid, A.DEPT, A.boxtype,
           B.CLIENT_NBR, B.MATTER_DESC, B.CLIENT_NAME, B.LAWYER_1, B.MATTER_NBR, B.CLOSE_DATE
    from NDXData A
    join IMFiledata B on A.CustBox =B.skpboxnbr
    where A.Custbox <> '' and b.FILE_STATUS <>'OUT' and A.DEPT <>''
) AS C
JOIN IMFileData D ON C.skpboxnbr = D.skpboxnbr

Open in new window

0
 

Author Closing Comment

by:RavenTim
ID: 40483706
Thanks Walter!
0

Featured Post

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

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

This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

610 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