CODEWITHSUNDEEP
sqlsql-server
Illuminati
Illuminati

Reputation: 565

Compare row values of a table with column values of another table and if true say true or false

I have two tables I need to compare row values from table 1 with column names of table 2 and if they match then need to update table 2 row values as true for the column names matched with row values of table 1. if they dont match then it should be updated with false.

Any pointers are highly appreciated.

Upvotes: 4

Views: 1949

Answers (2)

gofr1
gofr1

Reputation: 15977

You can use INFORMATION_SCHEMA view to obtain column names:

CREATE TABLE ##temp (
    column_name nvarchar(max),
    [flag] int
)

INSERT INTO ##temp
SELECT COLUMN_NAME, 0
FROM YourDB.INFORMATION_SCHEMA.COLUMNS  
WHERE TABLE_NAME = 'YOURTableName'

UPDATE t
SET [flag] = CASE WHEN a.ColumnToCheck IS NULL THEN 0 ELSE 1 END
FROM ##temp t
LEFT JOIN anotherTable a
    ON t.column_name = a.ColumnToCheck

Then join to your table and update, then pivot:

DECLARE @columns nvarchar(max), @query nvarchar(max)

SELECT @columns =STUFF((
SELECT ','+column_names FROM ##temp FOR XML PATH('')),1,1,'')

SELECT @query='
SELECT *
FROM ##temp
PIVOT (
    MAX([flag]) for column_name in ('+@columns+'
) as pvt'

EXEC sp_executesql @query

Better use dynamic SQL to add all column names automatically instead of manually assigning.

Upvotes: 1

bitch_cakes
bitch_cakes

Reputation: 126

Perhaps 

UPDATE TABLE2 
SET FLAG_Column = 'TRUE'
WHERE NAME_Column IN (
    Select c.name FROM sys.columns c JOIN sys.objects o ON 
    c.object_id = o.object_id
    WHERE o.name = 'TABLE1')

Upvotes: 0

Related Questions