Goto main content

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'.

SEE ALSO

{{ include("includes/database.sn") }}

AUTHOR

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

MODIFICATIONS

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

Edit

© 2021 extenso Inc. All rights reserved.