?
Solved

Help with update query

Posted on 2007-10-03
5
Medium Priority
?
227 Views
Last Modified: 2010-03-20
SQL Server 2000.
I have the following query which runs fine:

UPDATE MASTER55
  SET API__bNumber = t2."EMP ID", [Has__bAPI__bMatch__bRun__Q]='Yes'

FROM MASTER55 t1
JOIN [API].LaborWorkx_Live.dbo.vwCGHS_All_EmpList t2
  ON t1.SSN = right(t2.SSN, len(t2.SSN) - 1)
 AND t1.L__d__bName = t2."Last Name"
 AND t1.F__d__bName = t2."First Name"
 AND t1.mrREF_TO_MR Is null
 AND API__bNUMBER Is null
 AND [Has__bAPI__bMatch__bRun__Q] Is null

However, I need to change it to achieve the following. I need the field [Has__bAPI__bMatch__bRun__Q] to be set to Yes regardless of whether a successful match is made between the EMP ID and API__bNumber. This [Has__bAPI__bMatch__bRun__Q] field will be used to tell whether or not the query has ran for that particular record, so it needs to be set to Yes each time the query runs, whether its successful or not in finding a match. Please advise. Thanks.
0
Comment
Question by:isaacr25
  • 3
5 Comments
 
LVL 16

Accepted Solution

by:
SQL_SERVER_DBA earned 1400 total points
ID: 20008930
UPDATE MASTER55
  SET API__bNumber = t2."EMP ID",
[Has__bAPI__bMatch__bRun__Q]='Yes'
FROM MASTER55 t1
JOIN [API].LaborWorkx_Live.dbo.vwCGHS_All_EmpList t2
  ON t1.SSN = right(t2.SSN, len(t2.SSN) - 1);

UPDATE MASTER55
  SET API__bNumber = t2."EMP ID"
FROM MASTER55 t1
JOIN [API].LaborWorkx_Live.dbo.vwCGHS_All_EmpList t2
  ON t1.SSN = right(t2.SSN, len(t2.SSN) - 1)
 AND t1.L__d__bName = t2."Last Name"
 AND t1.F__d__bName = t2."First Name"
 AND t1.mrREF_TO_MR Is null
 AND API__bNUMBER Is null
 AND [Has__bAPI__bMatch__bRun__Q] Is null
0
 

Author Comment

by:isaacr25
ID: 20009115
SQL SERVER DBA,
     I ran this code with 2 records, one where there was a matching API__bNumber, and one where there wasn't one. [Has__bAPI__bMatch__bRun__Q] was only updated in the record with the match. I need this field to be updated even if there is no match.
0
 

Author Comment

by:isaacr25
ID: 20009709
Please see my last post. Thanks.
0
 

Author Comment

by:isaacr25
ID: 20013784
Hello... anyone there?
0
 
LVL 11

Assisted Solution

by:yuching
yuching earned 600 total points
ID: 20167865
Try this, change the join to left outer join

UPDATE MASTER55
  SET API__bNumber = t2."EMP ID", [Has__bAPI__bMatch__bRun__Q]='Yes'
FROM MASTER55 t1
LEFT OUTER JOIN [API].LaborWorkx_Live.dbo.vwCGHS_All_EmpList t2
  ON t1.SSN = right(t2.SSN, len(t2.SSN) - 1)
         AND t1.L__d__bName = t2."Last Name"  AND t1.F__d__bName = t2."First Name"
         AND t1.mrREF_TO_MR Is null AND API__bNUMBER Is null
         AND [Has__bAPI__bMatch__bRun__Q] Is null
0

Featured Post

Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

Question has a verified solution.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

850 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