how to pass values with respective update table in mysql -


when call stored procedure passing in lists of data input:

in pk varchar(max)  = “11;12;13” in forenam varchar(max)= “test1;test2;test3” in surnam varchar(max) = “tst1;tst2;tst3” in bdate varchar(max) = “2016-01-02; 2016-01-04; 2016-01-03” in comm varchar(max) = “comments1;comm2;comm3” in table_nam varchar(max)= “name1”

it should update table named “name1” below in 1 call sp

pk  forename    surname bdate   comments 11  test1   tst1    2016-01-02  comments1 12  test2   tst2    2016-01-04  comm2 13  test3   tst3    2016-01-03  comm3`      create definer=`username`@`%` procedure `sp_ukflmm_update_multitable`( in pk int,  in forenam varchar(255),  in surnam varchar(255), in bdate datetime, in comm varchar(1000),  in table_nam varchar(255)) begin  if table_nam in ('uk_flmm_demanddata','uk_flmm_completed_demanddata')  update table_nam set forename=forenam,surname=surnam,birthdate=bdate,comments=comm id=pk;  else if table_nam = 'uk_flmm_sequencedata'  update table_nam set comments=comm id=pk;  end if; end if;   end$$  delimiter

i have tried above sp updating 1 row @ time.so can please me sort out issue.

try replacing below condition in clause

where locate(replace(id,';',','),pk) > 0;

hope should solve problem.

this update multiple records. update 'comments1;comm2;comm3' 3 ids.

if need update below

id comm 1  comments1 2  comm2 3  comm3

then not work.

in case can follow below sp

create definer=`username`@`%` procedure `sp_ukflmm_update_multitable`( in pk int,  in forenam varchar(255),  in surnam varchar(255), in bdate datetime, in comm varchar(1000),  in table_nam varchar(255)) begin      declare loopincrement int;     declare looplimit int;     declare npk int;     declare nforename varchar(255);     declare nsurname varchar(255);     declare nbdate datetime;     declare ncomm varchar(1000);      set looplimit := 0;     set loopincrement := 0;      set looplimit := (length(pk) - length(replace(pk,";",""))) + 1;  while loopincrement < looplimit     if(loopincrement = looplimit - 1)         set npk := substring_index(pk,';',-1);         set nforename := substring_index(forenam,';',-1);         set nsurname := substring_index(surnam,';',-1);         set ncomm := substring_index(comm,';',-1);         set nbdate := substring_index(bdate,';',-1);     else             set npk := substring_index(pk,';',loopincrement+1);         set nforename := substring_index(forenam,';',loopincrement+1);         set nsurname := substring_index(surnam,';',loopincrement+1);         set ncomm := substring_index(comm,';',loopincrement+1);         set nbdate := substring_index(bdate,';',loopincrement+1);     end if;      if table_nam in ('uk_flmm_demanddata','uk_flmm_completed_demanddata')          update table_nam set forename=nforename,surname=nsurname,birthdate=nbdate,comments=ncomm id=npk;      else if table_nam = 'uk_flmm_sequencedata'      update table_nam set comments=ncomm id=npk;      loopincrement++;  end if; end if;  end while;     end$$  delimiter

note: sample sp implements scenario start with. improve if face issue.


Comments

Post a Comment

Popular posts from this blog

asynchronous - C# WinSCP .NET assembly: How to upload multiple files asynchronously -

i using winscp .net assembly uploading files. want upload multiple files asynchronous way. have created method works single upload. public class uploadingdata { private sessionoptions _sessionoptions; private session _session; //connection etc private void onconnected() { foreach (var fileinfo in localfileslist) { var task = task.factory.startnew(() => uploadfilesasync(fileinfo)); } } private async task uploadfilesasync(string file) { string remotefilepath = _session.translatelocalpathtoremote(file, @"/", "/test_data_upload"); var uploading = _session.putfiles(file, remotefilepath, false); //when done await task.run(() => thread.sleep(1000)); } } please suggest me correct way. thanks you can use backgroundworker class this: string[] images = new string[50]; foreach (var path in images) { backgroundworker
Read more

aws api gateway - SerializationException in posting new Records via Dynamodb Proxy Service in API -

i getting "__type": "com.amazon.coral.service#serializationexception" as reply in postman & in test console in api gateway trying post record directly dynamodb using api proxy services.. referring aws article - https://aws.amazon.com/blogs/compute/using-amazon-api-gateway-as-a-proxy-for-dynamodb/ here's mapping { "tablename": "tablenamegoeshere", "item": { "id" : "$context.requestid" "eventname" : "$input.path('$.eventname')", "timestamp" : $input.path('$.timestamp'), "answers": "$util.parsejson($input.path('$.answers'))" } } update: did asked ... , worked if try add array of json objects gives me above same error - here's trying now. please - couldnt find on google well #set($inputroot = $input.path('$')) { "tablename": "answer", "item": {
Read more

asp.net - Problems sending emails from forum -

i have been redesigning site has message board, has been written guy looked after site. it has few problems , written in .asp , uses .mdb files database. you can see whole thing here . feel free post if helps, test thing have set using original files , new database. the basic problem when posts message, supposed email sent them, have posted it. when replies, originator supposed message, replier has been posted. i’d have thought @ point in process there should script/file in postmessage.asp? the postmessage.asp page below. <% option explicit %> <!-- #include virtual="common/adovbs.inc" --> <html> <head> <meta http-equiv="content-type" content="text/html; charset=windows-1252"> <meta name="generator" content="microsoft frontpage 4.0"> <meta name="progid" content="frontpage.editor.document"> <title>new page 2</title> <base target="_self"
Read more