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>