MySQL - UPDATE and WHERE

289
August 23, 2017, at 6:52 PM

EDIT - I do not believe this is a duplicate, I have read through the other question, what I am trying to do is UPDATE just 1 column. However that 1 column could be any of 10.

To explain, if main='prop' then the column called prop needs to update to a 'Yes' otherwise leave as is and check next column, if main='hooker' then hooker will UPDATE to 'Yes' otherwise stay as is...etc.

So I've got a table and within this there 11 columns that I'm looking at right now

main -- prop -- hooker -- second_row -- flanker -- eight -- scrum_half...etc.

When a user has signed up 'main' has to be populated with one of the follow 10 column names. This is already done through a user input form. So for example 'main' might have the value prop

Under each following column it says 'Yes' or 'No', when a user has specified main is prop, the column prop has to say 'Yes' but unless the user ticks a checkbox this doesn't happen.

I can write a simple query such as UPDATE table SET prop='Yes' WHERE main='Prop' and set this up as a recurring event to continuously update the table. My problem is I then have to write another 9 queries to acommodate the other columns i.e.

UPDATE table SET hooker='Yes' WHERE main='Hooker'

Is there a way to do this so it includes all 10 in one query?

I tried using

UPDATE table SET (prop, hooker, second_row...) = 'Yes' WHERE ('prop', 'hooker', 'second_row'...) IN main

But this throws up a syntax error.

Answer 1

You can run it as a single query like this:

UPDATE table SET 
    prop = IF(main = 'prop','yes',prop), 
    hooker = IF(main = 'hooker','yes',hooker), 
    second_row = IF(main = 'second_row','yes',second_row), 
    ...
WHERE <whatever where clause you want>
READ ALSO
What is an IndexOutOfRangeException / ArgumentOutOfRangeException and how do I fix it?

What is an IndexOutOfRangeException / ArgumentOutOfRangeException and how do I fix it?

I have some code and when it executes, it throws a IndexOutOfRangeException, saying,

267
PHP translate words in sentense

PHP translate words in sentense

I have MYSQL database with 3 rows

203
PHP MySQL return columns with same value

PHP MySQL return columns with same value

For example I have table:

256