sql server update column based on another table
UPDATE
Sales_Import
SET
Sales_Import.AccountNumber = RAN.AccountNumber
FROM
Sales_Import SI
INNER JOIN
RetrieveAccountNumber RAN
ON
SI.LeadID = RAN.LeadID;sql server update column based on another table
UPDATE
Sales_Import
SET
Sales_Import.AccountNumber = RAN.AccountNumber
FROM
Sales_Import SI
INNER JOIN
RetrieveAccountNumber RAN
ON
SI.LeadID = RAN.LeadID;update all columns in one update
--Procedure to Update all columns in a given table (@Source) w/ Join from another Table (@Dest)
CREATE PROCEDURE UPDATE_ALL
@SOURCE VARCHAR(100),
@DEST VARCHAR(100),
@ID VARCHAR(100)
AS
DECLARE @SQL VARCHAR(MAX) =
'UPDATE D SET ' +
-- Google 'for xml path stuff' This gets the rows from query results and
-- turns into comma separated list.
STUFF((SELECT ', D.'+ COLUMN_NAME + ' = S.' + COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @DEST
AND COLUMN_NAME <> @ID
FOR XML PATH('')),1,1,'')
+ ' FROM ' + @SOURCE + ' S JOIN ' + @DEST + ' D ON S.' + @ID + ' = D.' + @ID
--SELECT @SQL
EXEC (@SQL)
--usage:
EXEC UPDATE_ALL 'source_table','destination_table','id_column'
-- Here's a hardcore way to do it with SQL SERVER. Carefully consider security and integrity before you try it, though.
--This uses schema to get the names of all the columns and then puts together a big update statement to update all columns except ID column, which it uses to join the tables.
--This only works for a single column key, not composites.Copyright © 2021 Codeinu
Forgot your account's password or having trouble logging into your Account? Don't worry, we'll help you to get back your account. Enter your email address and we'll send you a recovery link to reset your password. If you are experiencing problems resetting your password contact us
