Alasql: Let UPDATE, INSERT and DELETE work on data passed in parameter.

[pasapsi]

alasql('update ? as kpi_table set kpi_table.owner->selected = ? where kpi_table.owner.id=?', [[{id: 1, name: 'text', selected: true}, {id: 2, name: 'text2', selected: false}], false, 1])

[mathiasrw]

Can you elaborate on the problem?

[agershun]

? in UPDATE is not currently supported. We can add this if it is required.

[agershun]

Do we need the same for INSERT and DELETE?

[pasapsi]

hard to say, I'm just as solutions to their problems find various inconveniences in the library

[pasapsi]

@mathiasrw

The problem is that I can not update the data in the structure :(, based on the rules of writing a request for mysql or mssql

[agershun]

I will try to see can we implement ? in INSERT/DELETE/UPDATE statements.

[agershun]

The syntax can be:

    alasql('INSERT INTO ? (...) VALUES ...',[data]);
    alasql('UPDATE ? SET ... WHERE ...',[data]);
    alasql('DELETE FROM ? WHERE ...',[data]);

[pasapsi]

cool, change object occurs on the link?

[pasapsi]

Sample

var data = [{id: 1, text: 'test'}]
alasql('INSERT INTO ? (id, text) VALUES (2, "test 2")',[data]);

data =====> [{id: 1, text: 'test'}, {id: 2, text: 'test 2'}]

OR

var data = [{id: 1, text: 'test'}]
var res = alasql('INSERT INTO ? (id, text) VALUES (2, "test 2")',[data]);

data =====> [{id: 1, text: 'test'}]
res =====> [{id: 1, text: 'test'}, {id: 2, text: 'test 2'}]

[agershun]

The first option. The res for the first case will be 1 (only one record was inserted).

I will try to program this tonight.

[pasapsi]

Thank you for such a library as alasql :+1:

[agershun]

Probably, we do not need INSERT INTO ? because it easier to use:

    data.push(obj);

:) But for UPDATE and DELETE it can have sense.

[pasapsi]

Perhaps, but what to do if you want to implement such a request?

INSERT INTO
    `some_table` (`fld1`,`fld2`,`fld3`)
SELECT
    `src`.`fld1`,
    `src`.`fld2`,
    `src`.`fld3` 
FROM
    `some_table`  AS `src`
WHERE
    `src`.`fld` > 100;

[agershun]

This particular query already works.

But for parameter data:

    alasql('INSERT INTO ? (a) SELECT * FROM one',[data]);

[pasapsi]

It is clear, then I guess you don't need to yet.

[mathiasrw]

I like it.

I consider this notation as a short version of the following(ish):

alasql('create table tempdata')
alasql.tables.tmpdata = data
res = alasql(insert/update/delete sql into/from tempdata)
data = alasql('select * from tmpdata')
return res

And by that I recommend the first notation where res returns 1 for OK and data being changed by reference.

I have a feeling that if we support this with the update and delete it is good also to include the insert - mostly so people dont get confused. Also it makes it more clean when inserting forexample joining two tables / filtering.

[agershun]

Ok. Will try to do it...

[mathiasrw]

:)

[mathiasrw]

Any news on this?

[piotrkabacinski]

How's this issue going? Will it be fixed?

[mathiasrw]

@piotrkabacinski

Thanks for reaching out. This is an enhancement and, as such, is not subject to a fix, but and addision. As soon as someone finds the time and desire to look at it, it will be part of the library.

If you need to update data you must put it into a table first, update the table and select * to store the result into from the original data.

I have added this to the requested feature list to let people vore for what they would love to see next: http://feathub.com/agershun/alasql/+39

[piotrkabacinski]

@mathiasrw thanks for your answer and explanation. I will use the solution you suggested. You're right I shouldn't describe it as a bug. However, I'm looking forward for this feature, I've already voted for it :) You do a great job developing this project!

All the best,
Piotr

[mathiasrw]

Thank you :)

[benreibriongos]

Is this enhancement included in the current release?

[mathiasrw]

@benreibriongos Nope - no Pull Request for this feature has been received, so its not included yet...

[jiron12]

Most sweet actually would be SELECT:
alasql('SELECT * FROM test', [[{fistname: "John", lastname: "Doe", state: "Wisconsin"}]]);

...to get all the John Does of Wisconsin

[mathiasrw]

SELECT is already supported:

var data = [{a:1,b:10}, {a:2,b:20}, {a:1,b:30}];

var res = alasql('SELECT a, SUM(b) AS b FROM ? GROUP BY a',[data]);    

console.log(res); // [{"a":1,"b":40},{"a":2,"b":20}]

[jiron12]

No I'm talking about something more like this:

var data = [
  {fistname: "John", lastname: "Doe", state: "New York", birthdate: "1979-09-12"},
  {fistname: "John", lastname: "Doe", state: "Wisconsin", birthdate: "1964-03-02"},
  {fistname: "John", lastname: "Doe", state: "Wisconsin", birthdate: "1975-05-19"}
];

alasql('CREATE TABLE test');
alasql('INSERT INTO test SELECT * FROM ?', [data]);
alasql('SELECT * FROM test', [[{fistname: "John", lastname: "Doe", state: "Wisconsin"}]]);

console.log(res); // [
  {fistname: "John", lastname: "Doe", state: "Wisconsin", birthdate: "1964-03-02"},
  {fistname: "John", lastname: "Doe", state: "Wisconsin", birthdate: "1975-05-19"}
]

[matt212]

Hi,
i am using current version of alasql 0.4.5
i am unable to perform update on json array
js fiddle url:
alasql update jsfiddle

error:-

what is proper way to update jsonarray using alasql !

[mathiasrw]

I get this when clicking on the link

Error 404
We're truly sorry, but there is no such page.

[matt212]

@mathiasrw sorry , i edited my post with valid jsfiddle url please check now
FYI:
update reference link

[mathiasrw]

i am unable to perform update on json array

This issue (#348) is still open because this is still a feature request

[matt212]

@mathiasrw ,

current workaround for time being
jsfiddle url:
updated workaround

alasql('CREATE TABLE one');
alasql.tables.one.data = data;
var datasets=alasql('update one SET key=? WHERE inputtype=?;select * from one; truncate table one',['accesstypeaaaaa','multiselect']);

console.log(datasets[1])
/*check if table is truncated */
var red=alasql('select * from one')
console.log(red)

[rtxu]

Voted up on FeatHub

[jhenriquevissotto]

I have the same problem!