INSERT into table wherever update_time is latest

I have a table that has data about session. I want to insert hardcoded values for every new session row added at the latest time.

for example, given this table with 3 session rows information added at

2/10/2019 5:15 PM

I want to insert server1, and business values into the 3 rows (row 1-3) at the latest UDPATE_TIME, which in this case is

2/10/2019 5:15 PM

I use this query to get the latest update time:

$update_time = query "SELECT FORMAT(MAX([UPDATE_TIME]), 'M/d/yyyy h:mm tt') AS UPDATE_TIME FROM table"| Select -ExpandProperty UPDATE_TIME;

and i use this insert query to insert the values:

query "INSERT INTO table (SERVERNAME, Business_Name) VALUES ('server1', 'business')"

however, as you can see its inserting on a new row (row4), and instead i want to insert in rows that are already there 1-3, so that it becomes like this:

pseudo-code of query i am looking for:

query "INSERT INTO table (SERVERNAME, Business_Name) VALUES ('server1', 'business') wherever $update_time

Answer

I think you want an update. Something like this:

update [table]
    set business_name = @business_name
    where update_time = (select max(t2.update_time) from [table] t2);

Doing an update after the inserts seems dangerous — different threads could be inserting data at the same time. However this answers the question that you did ask.

Advertisement

Answer