SQL update from one Table to another based on a ID match

I have a database with account numbers and card numbers. I match these to a file to update any card numbers to the account number, so that I am only working with account numbers.

I created a view linking the table to the account/card database to return the Table ID and the related account number, and now I need to update those records where the ID matches with the Account Number.

This is the Sales_Import table, where the account number field needs to be updated:

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 gets replaced by NULL

Related Posts:

  1. SQL query to get the employee name and their manager name from the same table
  2. Update multiple columns in SQL
  3. Inserting multiple rows in a single SQL query? [duplicate]
  4. How do I UPDATE from a SELECT in SQL Server?
  5. How can I do an UPDATE statement with JOIN in SQL Server?
  6. T-SQL split string
  7. The SQL OVER() clause – when and why is it useful?
  8. SQL Server IF EXISTS THEN 1 ELSE 2
  9. SQL Query with SUM with Group By
  10. How do I perform an IF…THEN in an SQL SELECT?
  11. Conversion of a varchar data type to a datetime data type resulted in an out-of-range value in SQL query
  12. How to drop all tables from a database with one SQL query?
  13. SQL Server : Arithmetic overflow error converting expression to data type int
  14. How to calculate percentage with a SQL statement
  15. Difference between JOIN and INNER JOIN
  16. Query comparing dates in SQL
  17. Best way to do nested case statement logic in SQL Server
  18. What is the meaning of the prefix N in T-SQL statements and when should I use it?
  19. Query error with ambiguous column name in SQL
  20. How to drop all tables from a database with one SQL query?
  21. How to find sum of multiple columns in a table in SQL Server 2005?
  22. How can I delete using INNER JOIN with SQL Server?
  23. Conversion of a varchar data type to a datetime data type resulted in an out-of-range value in SQL query
  24. CREATE TABLE IF NOT EXISTS equivalent in SQL Server
  25. How to group by month from Date field using sql
  26. How can I get column names from a table in SQL Server?
  27. Is there a combination of “LIKE” and “IN” in SQL?
  28. Search text in stored procedure in SQL Server
  29. I want to use CASE statement to update some records in sql server 2005
  30. How to format a numeric column as phone number in SQL
  31. SQL – WHERE (X, Y) IN (A, B)
  32. sql query to return differences between two tables
  33. SQL: IF clause within WHERE clause
  34. I want to use CASE statement to update some records in sql server 2005
  35. how to increment integer Columns value by 1 in SQL
  36. How to select the last record of a table in SQL?
  37. SQL- Ignore case while searching for a string
  38. Update query using Subquery in Sql Server
  39. Using Excel VBA to run SQL query
  40. What is tableName.* in SQL
  41. What is the difference between “INNER JOIN” and “OUTER JOIN”?
  42. Case in Select Statement
  43. What do Clustered and Non-Clustered index actually mean?
  44. Self Join to get employee manager name
  45. SQL query to select dates between two dates
  46. Must declare the scalar variable
  47. INSERT statement conflicted with the FOREIGN KEY constraint – SQL Server
  48. INSERT statement conflicted with the FOREIGN KEY constraint – SQL Server
  49. SQL SELECT WHERE field contains words
  50. Drop a temporary table if it exists
  51. IDENTITY_INSERT is set to OFF – How to turn it ON?
  52. Update statement to update multiple rows
  53. Optimistic vs. Pessimistic locking
  54. Can a foreign key be NULL and/or duplicate?
  55. Why does NULL = NULL evaluate to false in SQL server
  56. When to use SELECT … FOR UPDATE?
  57. The target principal name is incorrect. Cannot generate SSPI context
  58. BCP error “Unable to open BCP host data-file”
  59. What is the difference between JOIN and UNION?
  60. How can I clone an SQL Server database on the same server in SQL Server 2008 Express?
  61. SQL join on multiple columns in same tables
  62. SQL Server : error converting data type varchar to numeric
  63. 1052: Column ‘id’ in field list is ambiguous
  64. 1052: Column ‘id’ in field list is ambiguous
  65. SQL Server reports ‘Invalid column name’, but the column is present and the query works through management studio
  66. How to parse XML data in SQL server table
  67. What is the equivalent of ‘describe table’ in SQL Server?
  68. Add a column with a default value to an existing table in SQL Server
  69. Remote table-Valued Function Calls are not allowed
  70. MS SQL compare dates?
  71. The backend version is not supported to design database diagrams or tables
  72. Determine ROW that caused “unexpected end of file” error in BULK INSERT?
  73. Determine ROW that caused “unexpected end of file” error in BULK INSERT?
  74. What is the maximum characters for the NVARCHAR(MAX)?
  75. Get size of all tables in database
  76. Exclude a column using SELECT * [except columnA] FROM tableA?
  77. Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 4 (Year)
  78. Why is SQL server throwing this error: Cannot insert the value NULL into column ‘id’?
  79. Bulk load data conversion error (truncation)
  80. How do I fix the multiple-step OLE DB operation errors in SSIS?
  81. SQL Server: Error converting data type nvarchar to numeric
  82. SQL “select where not in subquery” returns no results
  83. ListAGG in SQLSERVER
  84. SQL conditional SELECT
  85. ‘CREATE PROCEDURE’ must be the only statement in the batch (Erro)
  86. DATEDIFF function in Oracle
  87. How to calculate age (in years) based on Date of Birth and getDate()
  88. Solutions for INSERT OR UPDATE on SQL Server
  89. Text was truncated or one or more characters had no match in the target code page including the primary key in an unpivot
  90. How can I group by date time column without taking time into consideration
  91. MySQL error: Unknown column in ‘where clause’
  92. What are named pipes?
  93. SHOWPLAN permission denied in database ‘tempdb’. in sql server 2008
  94. Append table to an existing one: SQL Server
  95. How to order by column A and then by column B?
  96. SQL Server – An expression of non-boolean type specified in a context where a condition is expected, near ‘RETURN’
  97. “select * into table” Will it work for inserting data into existing table
  98. CASE IN statement with multiple values
  99. how to SELECT meta values that are not null?
  100. How to dump a Microsoft SQL Server database to a SQL script?

Leave a Comment