update statement with dynamically changing rows using prepared statement

Question

I am planning to execute an update statement using a prepared statement that makes use of a dynamically changing number of columns. for eg: in the first update statement I update only name and age of a table. in the second instance, I update age, city, state, country..etc. in the next instance, I update 150 columns like this.

can someone provide me what is the perfect approach for this in java? following is the example

If the user provides input for name and age then I update
UPDATE table1 set name = <> ,age = <>;

If the user provides input for city name state country and pin then the update statement should be like this-

UPDATE table1 set name = <>, city = <>,state= <>,country=<>, pin = <>;

You will not be able to use a single prepared statement for this. Build the SQL dynamically. — user11044402
– user11044402, Commented Jul 26, 2019 at 10:20

DarkRob · Accepted Answer · 2019-07-26 10:42:17Z

1

Build your sql query like this

  update demotable set col1 = case when @col1 is null then col1 else @col1 end

OR

Here @col is passed as value from front end.

from which you may create dynamic sql

  declare @col1 nvarchar(max)  /// from front you can pass your column value with its column name like: col1 = 'col1'

  declare @Query = 'update demotable set = ' + @col1   /// it create query as  update demotable set col1 = 'col1'

  PREPARE stmt1 FROM @Query ; 
  EXECUTE stmt1
  DEALLOCATE PREPARE stmt1;

I am new to MYSQL but this logic will surely work.

edited Jul 26, 2019 at 10:42

answered Jul 26, 2019 at 10:28

DarkRob

3,8231 gold badge12 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Naresh Kuppili Over a year ago

I have 200 columns to update. in different update statements i update the different columns..not sure how your sql query works. can you elaborate please

DarkRob Over a year ago

it depends how you are passing your input to the query.

Gordon Linoff · Accepted Answer · 2019-07-26 10:39:52Z

0

You can write one statement like this:

UPDATE table1
    SET name = COALESCE(?, name),
        age = COALESCE(?, age),
        city = COALESCE(?, city),
        . . .

Notes:

This assumes that the values are not being set to NULL.
The ? is a placeholder for a parameter. Don't munge query strings with user input.
Presumably you want a WHERE clause to limit what rows get updated.

answered Jul 26, 2019 at 10:39

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

1 Comment

Naresh Kuppili Over a year ago

I cannot use COALESCE, - as per the requirement, if name tag present in xml and value not present, we have to update with NULL. if name tag itslef not present in xml then we should leave the field as it is no update for that field.

Collectives™ on Stack Overflow

update statement with dynamically changing rows using prepared statement

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related