CODEWITHSUNDEEP
javasql
Naresh Kuppili
Naresh Kuppili

Reputation: 21

update statement with dynamically changing rows using prepared statement

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 = <>;

Upvotes: 1

Views: 456

Answers (2)

DarkRob
DarkRob

Reputation: 3833

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.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269753

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.

Upvotes: 0

Related Questions