Need help with join/lookup

[Michael Doré]

I am trying to figure out how to do the equivalent of a VLOOKUP in excel lookup for a value in one row and returning the value to my table. I can query and turn a TRUE if the row exists, but I cannot figure out how to return the value of SFDC_Id from the SFDC_Users table to the field in eidtable_diff.

This is being run against 270K rows so whatever way is more efficient would be appreciated.

Any help is appreciated.


/* Make new table */
CREATE TABLE sfdc_users (SFDC_Id TEXT(22) NULL COMMENT '');
ALTER TABLE sfdc_users ADD COLUMN `FederationIdentifier` TEXT(20) NULL COMMENT '';

/* Load last SFDC_USER file */
LOAD DATA INFILE 'FAKE PATH' INTO TABLE sfdc_users CHARACTER SET utf8 FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n';


/* Make new table */
CREATE TABLE eidtable_diff (`Unique_ID` VARCHAR(9) NULL COMMENT '');

/* Load last DIFF file */
LOAD DATA INFILE 'FAKE PATH' INTO TABLE eidtable_diff CHARACTER SET utf8 FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';


/* Add my column to return the SFDC_Id from the SFDC_users table */
ALTER TABLE eidtable_diff ADD COLUMN `SFDC_Id_diff` TEXT(22) NULL COMMENT '';

/* This correctly sets to true where the user exists in the table - but I also want to return the value of the ID from the SFDC_USERS table */
UPDATE eidtable_diff SET IsInSFDC = 1 WHERE (ID > 1 AND EXISTS (SELECT * FROM sfdc_users WHERE `eidtable_diff`.`Unique_ID`=`sfdc_users`.`FederationIdentifier`));