Update Query in MYSQL?

Question

I am trying to write an update query like this:

Update entryTable set type='String' Where ID='Entry_001' or SecondID='Entry_002' or ThirdID="Entry_003" or BackupID="Entry_004"

But what it does is, it updates my table if it finds any of the above parameter i.e ID,SecondID,ThirdID,BackupID.

But i have to write an update query where user can pass any parameter and i have to filter based on those parameters passed by the user, i.e. the user can pass only ID or he can pass both ID and SecondID or He can pass SecondID or ThirdID or all or He can pass only BackupID and so on. Basically i want to write an update query that will accept any parameter and update based on those parameters. Hope you have understood my question. Thanks in advance.

Answer 1

You should try composing your query more carefully instead of always adding all the conditions. It's not clear what language or framework you're using to get this query, so I can't offer specific advice there.

The result is you'd start with this:

UPDATE entryTable SET type='String' WHERE

Then you'd append conditions one by one. It's often best to add these to an array and then join the array with 'OR' or 'AND' as required:

[ [ "ID=?", "Entry_001" ], [ "SecondID=?", "Entry_002" ] ]

That would be transformed into:

[ "UPDATE entryTable SET type='String' WHERE ID=? OR SecondID=?", "Entry_001", "Entry_002" ]

Then you'd use the escaping feature of your database library to bind against those placeholders and values together.

You may want to try and fetch the record before blindly issuing an update, too, then update based on your primary key alone:

SELECT ID FROM entryTable WHERE ID='Entry_001' ...

Then, if that returns a single record, perform an update:

UPDATE entryTable SET type='String' WHERE ID=?

If it returns multiple records, you'd prompt the user to better define their search.