Re: Need help with join/lookup

[Peter Brawley]

Instead of a Create Table cmd with one column then dozens of Alter Table cmds, why not just issue one Create Table cmd!?

> IsInSFDC TEXT(1) NULL
> IsActiveInSFDC TEXT(1) NULL
> MakeInactive TEXT(1) NULL
> InsertInSFDC TEXT(1) NULL ;
> UpdateInSFDC TEXT(1) NULL

Use TinyInt or Bool, or Char(1) if you prefer Y/N. Text is for, um, text.

> UPDATE eidtable_diff SET IsInSFDC = 1 WHERE (ID > 1 AND
> EXISTS(SELECT * FROM sfdc_users WHERE `eidtable_diff`.`Unique_ID`=`sfdc_users`.`FederationIdentifier`));

It's almost always a mistake to store calculated results like that, because it's redundant, and it introduces the possibility of skew. Better to define a View, or since 5.7.6 a Generated column, or just a query eg given eidtable_diff=somevalue ...

select !isnull(select federationidentifier from sfdc_users where federationidentifier=somevalue);