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