Solved

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

Posted on 2014-12-05
2
104 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
2 Comments
 
LVL 10

Accepted Solution

by:
Walter Padrón earned 500 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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
2008 to 2016 SQL migration.. 5 29
STDEVP in SQL 2 38
SQL Server R2 Stored procedure performance 8 33
Sql query for filter 12 23
Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

746 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

12 Experts available now in Live!

Get 1:1 Help Now