update

Update databases.

UPDATE

NAME

update - Update databases.

SYNOPSIS

update(( [db:pointer,] tables:string, wantarray:true|false, count:integer, fields:context, snc:bool, user:integer, join:string, having:string, selectby:string, orderby:string, groupby:string, limit:string, args:string, uid:integer, page:integer, prefix:true|false, countperpage:integer);

DESCRIPTION

This function is used to update elements in tables.

Since the database does not support page, limit, groupby having, join. A select is perform to get the list of uid and than an update is perform on this list. As a consequence, the field uid must be present in the table.

PARAMETERS

tables: Specify list of tables commads separated.

snc: Update fields sn_mdate, sn_muid

user: User id for field sn_muid

wantarray: By default rows are return as a context with the name of the column. If this parameter is true, it return only the value.

count: If parameter page is used, then the function must calculate the number of element. If you already have this number, you can use this parameter to initialize it.

fields: Specify the list of fields commas separated.

join: Specify join statement for select.

having: Specify having statement for select.

selectby: Specify condition for the WHERE statement. See args \? is replase with ?.

noargs: By default ? in selectby are replace by values in args. If noargs is true, no replacement is done.

args: Specify arguments wihin selecby to quote and replace value. All ? are replace with one arguments.

orderby: Specify ORDER BY statement.

groupby: Specify GROUP BY statement.

limit: Specify LIMIT statement.

uid: Specify UID for the WHERE statement.

page: Specify page number based on count and countperpage.

prefix: Prefix name of field with table name followed by _.

countperpage: Number of elements in a page.
db: Specify alternate database pointer as return from connect function; Starting from version 5.6.2 if db is undefined, it will fall back to the default database of the project.

RETURN

This function return an has array with thw following elements:

sql: Build SQL statement.

nbcols: Number of columns in result.

offset: Offset calculated when page is specify. If not, the value is -1.

sqlerr: Error message if any.

count: Calculated count (or the one specify in parameter) if page is specify. -1 otherwise.

error: Specify if we got an error or not.

sqlcode: SQL error code. 0 if no error.

nbrows: Number of rows in result.

rows: List if rows. The rows can be an array of hash or an array of array if wantarray is true.

EXAMPLES

Note: In the followings examples, the _ between the { should be removed to make it work.

res={_{
    update(tables:"sn_test1",selectby:"table_name = '?'",args:["sn_test1"],fields:{"table_name":"sn_test3"});
                a=sql("drop table IF EXISTS ? ;","sn_test1");
                a=sql("drop table IF EXISTS ? ;","sn_test2");
                a=sql("create table sn_test1 (uid int, firstname varchar(255), lastname varchar(255");
                a=sql("create table sn_test2 (uid int, uid_sn_test_1 int, firstname varchar(255), lastname varchar(255))");
                for(i=1;i<=5;++i)
                        a=sql("insert into sn_test1 values('?', '?', '?');",i, "f" .+ i,"l" .+ i );
                        for(j=1;j<=5;++j)
                                a=sql("insert into sn_test2 values('?', '?', '?', '?');",i,j,"g" .+ i .+ j,"h" .+ i .+ j);
                        endfor
                endfor
                }}. return res=.

res={_{ update(); }}. return .*tables must be specify.*
res={_{ update(tables:"sn_test1"); }}. return .*fields must be specify.*
res={_{ update(tables:"sn_test1",fields:"aaaa",page:5,limit:"abc"); }}. return .*Parameter fields is not a context.*

res={_{
                        a=select(tables:"sn_test1",uid:1); a.rows;
                        a=update(tables:"sn_test1",uid:1,fields:{firstname:"laplante"}); a.error;
                        a=select(tables:"sn_test1",uid:1); a.rows;
        }}. return res=\[{"uid":"1","lastname":"l1","firstname":"f1"}\]false\[{"uid":"1","lastname":"l1","firstname":"laplante"}\].

res={_{
                        a=select(tables:"sn_test1"); a.rows;
                        a=update(tables:"sn_test1",page:1,countperpage:2,fields:{firstname:"laplante"}); a.error;
                        a=select(tables:"sn_test1"); a.rows;
        }}. return res=\[{"uid":"1","lastname":"l1","firstname":"laplante"},{"uid":"2","lastname":"l2","firstname":"f2"},{"uid":"3","lastname":"l3","firstname":"f3"},{"uid":"4","lastname":"l4","firstname":"f4"},{"uid":"5","lastname":"l5","firstname":"f5"}\]false\[{"uid":"1","lastname":"l1","firstname":"laplante"},{"uid":"2","lastname":"l2","firstname":"laplante"},{"uid":"3","lastname":"l3","firstname":"f3"},{"uid":"4","lastname":"l4","firstname":"f4"},{"uid":"5","lastname":"l5","firstname":"f5"}\].

res={_{
                        a=update(tables:"sn_test1",uid:1,fields:{frstname:"laplante"}); a.error;
                        a.sqlerr;
        }}.return res=trueUnknown column 'frstname' in 'field list'.

AUTHOR

Written by Pierre Laplante, <laplante@sednove.com>

MODIFICATIONS

1.0 2014-09-09 21:24:14 laplante@sednove.com

Edit