sqlpage.variables(method) and UPDATE

[DSMejantel]

Hello,

I want build a form with a number variables of field to update some values like this :

-- Formulaire de mise à joure des plats 
SELECT 
    'form' as component,
    'Mettre à jour'   as validate,
    'def_repas_semaine_confirm.sql' as action;
    
SELECT positions.position||' du '||creneau as label, 
       plat_id AS name, 
       'text' as type, ardoise.icon as prefix_icon, $largeur as width, 
       name as value 
       FROM ardoise JOIN plats on ardoise.plat_id=plats.id JOIN positions on positions.id=plats.position_id ORDER BY ardoise.position;

But I have some problems with to register the change with update :

--Insertion dans la base
UPDATE plats
SET name=CAST(value AS TEXT) WHERE id=CAST(key AS INTEGER)
FROM JSON_EACH(sqlpage.variables('post'));

I receive this advertissement for this issue
In "def_repas_semaine_confirm.sql": The following error occurred while executing an SQL statement:
error returned from database: (code: 1) no such column: value (at statement byte offset 29)
The SQL statement sent by SQLPage was:
UPDATE plats SET name = CAST(value AS TEXT) WHERE id = CAST(key AS INTEGER)

Do I use a bad method ?

[DSMejantel]

I just.understand than a SET IS not like a SELECT and it can't work with FROM

[lovasoa]

Hello !

The error message is indeed confusing.

Since sqlpage does not enforce semicolons at the end of statements, and both

UPDATE plats
SET name=CAST(value AS TEXT) WHERE id=CAST(key AS INTEGER)

and

FROM JSON_EACH(sqlpage.variables('post'));

are syntactically valid (in different sql dialects), you do not get the parse error that would be expected here.

[lovasoa]

The correct syntax for what you want to do is

UPDATE plats
SET name=CAST(var.value AS TEXT)
FROM JSON_EACH(sqlpage.variables('post')) as var
WHERE plats.id=CAST(var.key AS INTEGER);

[lovasoa]

You can read the full syntax documentation here: https://www.sqlite.org/lang_update.html

[DSMejantel]

Wonderful !
Merci pour tes explications encore une fois claires et pédagogiques. Cela fonctionne en rajoutant

as var

[lovasoa]

Ce n'est pas le as var qui est important, c'est l'ordre: FROM avant WHERE.

[DSMejantel]

C'est la rentrée, j'ai besoin d'un stage de remise à niveau après une longure pause numérique !