Complex update statement? - UPDATE [Table1] SET [Column] = [Table2.ValueColumn] WHERE [Table1.ID] = [Table2.ID]

[RFBomb]

I have 2 databases that I am working with, and I need to update data in DB1 from the data residing in DB2.

My current method I've been using (prior to discovering this library) was to simply drop the table in DB1 and insert from DB2 to essentially recreate it altogether. The problem with this is due to the table size, this is quite time-consuming (sometimes >2 minutes depending on the computer's speed and connection to the remote db)

Back when I initially was trying to get this accomplished, I saw a bunch of talk about how you can loop through the columns and perform a where statement to update just the ones that need updating, which is what I'd like to implement if possible. (I gave up since there i was pressed for time but now have some time to try and improve.)

This site pretty much details what I want to do, but I am wondering how to achieve that using this library.

Here is my current string to perform the insertion, which works.

SELECT = $"SELECT {ColumnList} INTO [{LocalName}] FROM [{ConnectionString}].[{LinkedTableName}]";

I couldn't figure out how to translate that into SqlKata.

Below is a working query that have generating and running inside of a for-loop to update the local table from a remote table, column by column

qry = $"UPDATE [{LocalName}] AS LocalTable " +
      $"INNER JOIN " +
      $"( Select [{PKey}], [{col}] FROM [{ConnectionString}].[{LinkedTableName}] ) AS RemoteTable " +
      $"ON RemoteTable.[{PKey}] = LocalTable.[{PKey}] " +
      $"SET LocalTable.[{col}] = RemoteTable.[{col}] " +
      $"WHERE LocalTable.[{col}] <> RemoteTable.[{col}]";

**edited with working query string for MSAccess, which is what I'm building for - Note that I have already created a compiler for it, but may need to do further tweaks if sqlkata can come close to buliding the above statement

This is the closes I could get, which doesn't do the job:

Query:

Query SQL = new Query($"{LocalName} as L");
SQL = SQL.Join($"[{ConnectionString}].[{LinkedTableName}] as R", $"L.{PKey}", $"R.{PKey}");
SQL = SQL.AsUpdate($"L.{col}", $"R.{col}");
SQL = SQL.WhereNot($"R.{col}", $"L.{col}");

Compiled: "UPDATE [tblRobot] AS [L] SET [L].[ArrivalDate] = 'R.ArrivalDate' WHERE NOT ([R].[ArrivalDate] = 'L.ArrivalDate')"

it threw out the join statement altogether. It also converted what should be a reference to a column R.[ArrivalDate] to a value. I could use WhereRaw here, but there is no UpdateRaw or any update that accepts a column reference (as far as I can tell)

[ahmad-moussawi]

Hello, check this thread https://github.com/sqlkata/querybuilder/issues/370 it may help you, and let me know