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, 2022 in Database by Kithuzzz
• 38,020 points
1,182 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, 2022 in Database by Kithuzzz
• 38,020 points
960 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, 2022 in Database by Neha
• 9,020 points
8,941 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, 2022 in Database by Vaani
• 7,070 points
2,812 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, 2022 in Database by gaurav
• 23,260 points
1,006 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, 2022 in Database by Kithuzzz
• 38,020 points
836 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, 2022 in Database by narikkadan
• 63,680 points
868 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, 2022 in Others by Avinash
• 240 points
4,695 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, 2022 in Database by Vaani
• 7,070 points
797 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, 2022 in Database by Vaani
• 7,070 points
523 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, 2022 in Database by Neha
• 9,020 points
19,815 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP