SQL Update from One Table to Another Based on a ID Match

0 votes

Account and credit card numbers are stored in a database I hold. To ensure that I am just working with account numbers, I compare these to a file and update any card numbers to the account number.

I built a view connecting the table to the account/card database to return the Table ID and associated account number. Now I need to update the records where the ID and account number match.

The account number field in the Sales Import database needs to be modified.

LeadID AccountNumber
147 5807811235
150 5807811326
185 7006100100007267039

And this is the RetrieveAccountNumber table, where I need to update from:

LeadID AccountNumber
147 7006100100007266957
150 7006100100007267039

I tried the below, but no luck so far:

UPDATE [Sales_Lead].[dbo].[Sales_Import] 
SET    [AccountNumber] = (SELECT RetrieveAccountNumber.AccountNumber 
                          FROM   RetrieveAccountNumber 
                          WHERE  [Sales_Lead].[dbo].[Sales_Import]. LeadID = 
                                                RetrieveAccountNumber.LeadID) 

It updates the card numbers to account numbers, but the account numbers get replaced by NULL

Aug 22 in Database by Kithuzzz
• 11,640 points
45 views

No answer to this question. Be the first to respond.

Your answer

Your name to display (optional):
Privacy: Your email address will only be used for sending these notifications.

Related Questions In Database

0 votes
0 answers

Oracle SQL: Update a table with data from another table

Table 1: id name ...READ MORE

Aug 13 in Database by Kithuzzz
• 11,640 points
44 views
0 votes
1 answer

How to drop all tables from a database with one SQL query?

Use the INFORMATION_SCHEMA.TABLES view to get the ...READ MORE

answered Feb 4 in Database by Neha
• 8,920 points
293 views
0 votes
1 answer

Which SQL query is used to find Nth highest salary from a salary table

If you want to find nth Salary ...READ MORE

answered Feb 14 in Database by Vaani
• 7,020 points
460 views
0 votes
1 answer

How to run a SQL query on an Excel table?

On Excel tables, how to construct and ...READ MORE

answered Apr 11 in Database by gaurav
• 18,960 points
152 views
0 votes
0 answers

How to create a table from select query result in SQL Server 2008

I tried to build a table from ...READ MORE

Sep 2 in Database by Kithuzzz
• 11,640 points
37 views
0 votes
1 answer

How do I create a table based on another table

Although CREATE TABLE AS... SELECT does exist ...READ MORE

answered Sep 12 in Database by narikkadan
• 19,680 points
28 views
0 votes
1 answer

How can we UPDATE from a SELECT in an SQL Server

We can firstly use SELECT statement to fetch ...READ MORE

answered May 27 in Others by Avinash
• 240 points
64 views
0 votes
1 answer

How do I UPDATE from a SELECT in SQL Server?

MERGE INTO YourTable T USING ...READ MORE

answered Feb 3 in Database by Vaani
• 7,020 points
119 views
0 votes
0 answers

How do I UPDATE from a SELECT in SQL Server?

INSERT INTO Table (col1, col2, col3) SELECT col1, ...READ MORE

Feb 4 in Database by Vaani
• 7,020 points
44 views
0 votes
1 answer

SQL SELECT WHERE field contains words

Use this query to include any of words: SELECT * ...READ MORE

answered Feb 21 in Database by Neha
• 8,920 points
2,030 views
webinar REGISTER FOR FREE WEBINAR X
Send OTP
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP