sql 2021-06-01 | Slack Archive

What would the SQL look like for that update @orlandomr27?

(you can always do (jdbc/execute-one! ds ["arbitrary SQL for updating DB" ...]) which is why I’m asking)

Given a collection of :code/`:status` pairs that could have arbitrary values, I don’t think you could construct a single SQL statement that updated all the matching rows.

isak19:06:56

#Also sent to the channel

You can if your database supports something like OPENJSON (MS SQL, postgres, sqlite with extensions):

-- Test table
declare @Person as table(
    id int primary key,
    email nvarchar(max)
);

insert into @Person(id, email)
values 
    (1, '[email protected]')
   ,(2, '[email protected]')
   ,(3, '[email protected]')

-- This would be passed in as a SQL parameter
declare @emailUpdates nvarchar(max) = N'[
    {"id": 1, "email": "[email protected]"},
    {"id": 2, "email": "[email protected]"}
]';

-- The single SQL statement:
update a
set email = b.email
from @Person a
join openjson(@emailUpdates, '$') with (
    id int,
    email nvarchar(max)
) b on a.id = b.id

seancorfield00:06:00

So perhaps instead of “single statement” you mean “single transaction”?

theequalizer7300:06:08

Exactly, I meant a single transaction. I have not yet written a SQL query for that

seancorfield00:06:40

Are you using next.jdbc? If so: https://cljdoc.org/d/com.github.seancorfield/next.jdbc/1.2.659/doc/getting-started#datasources-connections--transactions

theequalizer7301:06:06

Will take a look. Thanks

isak19:06:56

replied to a thread:Given a collection of `:code`/`:status` pairs that could have arbitrary values, I don’t think you could construct a _single_ SQL statement that updated all the matching rows.

You can if your database supports something like OPENJSON (MS SQL, postgres, sqlite with extensions):

-- Test table
declare @Person as table(
    id int primary key,
    email nvarchar(max)
);

insert into @Person(id, email)
values 
    (1, '[email protected]')
   ,(2, '[email protected]')
   ,(3, '[email protected]')

-- This would be passed in as a SQL parameter
declare @emailUpdates nvarchar(max) = N'[
    {"id": 1, "email": "[email protected]"},
    {"id": 2, "email": "[email protected]"}
]';

-- The single SQL statement:
update a
set email = b.email
from @Person a
join openjson(@emailUpdates, '$') with (
    id int,
    email nvarchar(max)
) b on a.id = b.id

2021-06-01

Channels