-1

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.

James V
  • 60
  • 1
  • 7
  • Does this help: ´Update table set prop='yes', hooker='yes', .... WHERE (prop = 'yes' AND second_row='yes' AND ...)´ – Steven Lemmens Aug 23 '17 at 12:14
  • Possible duplicate of [Update multiple columns in SQL](https://stackoverflow.com/questions/9079617/update-multiple-columns-in-sql) – RealCheeseLord Aug 23 '17 at 12:15
  • I have read through that but it doesn't seem to quite fit, my problem is I only want to update one column, which column I update is entirely dependant on the value of the other column. If that makes sense? – James V Aug 23 '17 at 12:17
  • Your design is broken. See about database design. – Strawberry Aug 23 '17 at 12:19
  • Thanks Steven but not quite unfortunately, the WHERE clause bescially should be main='prop' if the value is prop then the column prop needs to change to Yes if it isn't then no change and check next column – James V Aug 23 '17 at 12:20
  • Thanks for the comment Strawberry, I don't believe it is though, a user can specify their main position but also say which others they can play. The problem is in search results if they haven't ticked their main position then it doesn't come up. I can update this using 10 queries but was hoping there was a way to put all 10 queries into 1 – James V Aug 23 '17 at 12:21
  • It's not a question of faith. A database table is not a spreadsheet. A normalised design might be a separate table which stores the id of the player and the id of the various positions in which that player might play (one position per row). A further flag column might indicate which of those positions is 'main'. – Strawberry Aug 23 '17 at 12:24
  • That makes sense. Unfortunately it's bit late to go back and change it now as it is on a live site. Can you tell I've learnt as I go!? Is there a way to fix this as it is at the moment? If not I'll just have to run with the 10 queries. – James V Aug 23 '17 at 12:25
  • If you don't change it now, you will come to regret it. – Strawberry Aug 23 '17 at 12:26
  • Is there any reason this is being downvoted? I feel it is a legitimate question, just because it looks like I've messed up my database a bit doesn't mean it's a bad question surely? – James V Aug 23 '17 at 12:26

1 Answers1

0

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>
Vatev
  • 7,493
  • 1
  • 32
  • 39