How can I convert this SELECT query into an update query that actually works, for some reason when I try and assign values to my variables the SELECT statement doesn’t even work.
UserlabelId UUID;
UserlabelId2 UUID;
UserlabelId3 UUID;
SELECT INTO UserlabelId id from labels where name = 'label1';
SELECT INTO UserlabelId2 id from labels where name = 'label2';
SELECT INTO UserlabelId3 id from labels where name = 'label3';
SELECT
CASE
WHEN (u.role_id == UserLabelId) THEN 'L1'
WHEN (u.role_id == UserLabelId2) THEN 'L2'
ELSE 'L3'
END AS new_user_label
FROM transactions t
INNER JOIN users u ON u.id = t.sold_by_user_id
5
To be able to use variables you have to utilize anonymous blocks or create a store procedure or function
An example of an anonymous block similar to your needs
do $$
declare
UserlabelId UUID;
UserlabelId2 UUID;
UserlabelId3 UUID;
rows_affected int;
begin
select gen_random_uuid() into UserlabelId;
select gen_random_uuid() into UserlabelId2;
select gen_random_uuid() into UserlabelId3;
raise notice 'UserlabelId: %', UserlabelId;
raise notice 'UserlabelId2: %', UserlabelId2;
raise notice 'UserlabelId3: %', UserlabelId3;
update test set user_uuid=UserlabelId
where user_uuid is not null and first_name='John';
get diagnostics rows_affected = row_count;
raise notice 'Rows updated: %', rows_affected;
exception
when others then
raise notice 'The following error occurred: %.', sqlerrm;
end $$;
the output
UserlabelId: df618f55-755e-498e-ba95-4d5d85024c52
UserlabelId2: a43d9348-7d8b-46f3-9e2a-9a557563865c
UserlabelId3: e995fde3-65d3-4409-8c33-6e73b867c5c5
Rows updated: 1