-3

Using the example table below, as of now I have: UPDATE wc_bidsys_picklist SET vipplays = 'YES' WHERE playoftheday = 'YES';
Is this much correct? If so how can I also add: "within a specific range that is set by the column (game_date) field values?" Range between '2016-06-29 00:00:00' through '2017-06-29 00:00:00'. Is this even possible?

My specific question in detail: (partially answered above)
Using my example below for a table called 'wc_bidsys_picklist': Using SQL is there a way to update the value ('NO' to 'YES') of each field in a specific column (vipplays) only if it does NOT equal the field of a different specific column (playoftheday) so the fields in both of these columns end up having matching values ('YES') while within a specific range set by a 3rd column (game_date)?

In more simple terms I need to say "In column (game_date) from '2016-06-29 00:00:00' through '2017-06-29 00:00:00' if the field value in the column (playoftheday) = 'YES' and the field value in the column (vipplays) = 'NO' then UPDATE the field value in the column (vipplays) to 'YES'. How, if possible, can this be written in SQL?

UPDATE: After a few updates to the fiddle this one seems to work.
My fiddle: http://sqlfiddle.com/#!9/69cf4/1/0

CREATE TABLE wc_bidsys_picklist
    (`id` int, `game_date` datetime, `playoftheday` varchar(5), `vipplays` varchar(5))
;

INSERT INTO wc_bidsys_picklist
    (`id`, `game_date`, `playoftheday`, `vipplays`)
VALUES
    (1, '2016-07-01 00:00:00', 'YES', 'NO'),
    (2, '2016-07-03 00:00:00', 'YES', 'YES'),
    (3, '2016-07-04 00:00:00', 'YES', 'NO'),
    (4, '2016-07-04 00:00:00', 'YES', 'NO'),
    (5, '2016-07-06 00:00:00', 'YES', 'NO'),
    (6, '2016-07-07 00:00:00', 'YES', 'YES'),
    (7, '2017-06-08 00:00:00', 'YES', 'NO'),
    (8, '2017-06-29 00:00:00', 'YES', 'NO')
;
UPDATE 
  wc_bidsys_picklist
SET 
  vipplays = 'YES' 
WHERE 
  playoftheday = 'YES'
AND
  (game_date BETWEEN '2016-07-01 00:00:00' AND '2017-06-29 00:00:00');


On a side note I would like to reiterate that I am relatively new to this community and programming. I don't understand why all the immediate down votes. It seems like members are more interested in correcting posts and bullying new users rather than helping you out. And apparently I'm not the only one: https://hackernoon.com/the-decline-of-stack-overflow-7cb69faa575d It's an interesting read.

kma1289
  • 109
  • 1
  • 8
  • Add an AND date_added >= '2016-07-20 00:00:00' AND date_added <= ..... you can use NOT or <> for exclusions – clearshot66 Jun 30 '17 at 14:15
  • 2
    See: [Why should I provide an MCVE for what seems to me to be a very simple SQL query?](https://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query) – Strawberry Jun 30 '17 at 14:18
  • 1
    phpmyadmin is a sql ui - it is not relevant to the question :) – treyBake Jun 30 '17 at 14:26
  • 1
    @ThisGuyHasTwoThumbs- Also, WP tag is not relevant to the question.... – random_user_name Jun 30 '17 at 14:27
  • I would be happy to show you. I am new to this and learning how to do just that at the moment as I didn't even know that was possible. It will be my first time. I am finding this site is not very patient with noobs. May have misunderstood and maybe it is only for highly experienced programmers. Doing the best I can here while hoping to get some help and trying to contribute something that may help someone in the future. Not sure why all the down votes. Maybe there is another site I can try. I will add the example. thanks. – kma1289 Jun 30 '17 at 14:37
  • @Strawberry - thanks! It was unintentional. While I'm not sure it matters at this point, I will remove it. Thanks for the dialog, helps me to be a better SO steward! – random_user_name Jun 30 '17 at 15:57
  • Example using [SQL Fiddle](http://sqlfiddle.com/#!9/69cf4/1/0) has been added. – kma1289 Jun 30 '17 at 19:23

1 Answers1

2

I think this this will work:

UPDATE wc_bidsys_picklist SET vipplays = 'YES' WHERE playoftheday = 'YES' AND game_date='2016-07-20 00:00:00';

Change the date as needed.

random_user_name
  • 25,694
  • 7
  • 76
  • 115
Anandhu Nadesh
  • 672
  • 2
  • 11
  • 20
  • This misses the key component of the question: only update it when it does NOT match the value of another column... Further, setting the date to an `=` limits you too much due to the time component of the date, you either need to cast the date to include the whole day, or else use range(s) to select an entire date. – random_user_name Jun 30 '17 at 14:26
  • @cale_b Why does SET vipplays = 'YES' WHERE playoftheday = 'YES not answer the question? Yes, it may overwrite the vipplays "Yes" values to "Yes" again but it still gets the job done. If you wanted to eliminate that redundancy you could add another AND to the where to filter out the rows with vipplays="yes" but either way, you're still doing operations on those rows. – André Fecteau Jun 30 '17 at 14:30
  • Will this set a range? Or will this just start at `game_date='2016-07-20 00:00:00'` and go forward? Or will this only change values on this specific game_date? – kma1289 Jun 30 '17 at 14:43
  • 1
    @Dambr7 only with the specific date. See this Stackoverflow thread for how to query between two dates (it's pretty simple) https://stackoverflow.com/questions/3822648/how-do-i-query-between-two-dates-using-mysql – André Fecteau Jun 30 '17 at 14:46
  • Because OP asked *where the fields are different*, NOT *where the field matches a specific value* – random_user_name Jun 30 '17 at 15:28
  • @AndréFecteau This answer was helpful although did not fully answer my question. I updated my question with an [SQL Fiddle](http://sqlfiddle.com/#!9/69cf4/1/0) that is working with a date range added as well. If you care to update your answer I would gladly mark it as the correct answer to the question. Thanks. – kma1289 Jun 30 '17 at 19:18
  • Tried to edit the answer to reflect my fiddle which is now correct including the date range and it gets rejected. Ridiculous... nelek reviewed this 18 hours ago: Reject This edit deviates from the original intent of the post. Even edits that must make drastic changes should strive to preserve the goals of the post's owner. I am the owner of the post. This was the intent! More members need to read the entire post & look at the Fiddles before jumping to conclusions. – kma1289 Jul 02 '17 at 12:49