Results 1 to 14 of 14
  1. #1
    Join Date
    Mar 2014
    Posts
    8

    Question Unanswered: Replacing things in relational database - JOIN, maybe?

    Hello,

    I have a newbie question. It's been about a year since I briefly dabbled into the world of MySQL, and I seem to have forgotten everything!

    I have a couple of tables with some shared values, and I need (I think) to do some kind of JOINing to replace some data. I'm sure it's relatively straightforward, but I just can't get my head around it. Here's the problem:

    I have two tables:
    tickets
    events

    tickets has (amongst other columns) event_id and ticket_price.

    events has (amongst other columns) event_id and event_name.

    event_id is (as far as I can tell) the shared thing/constraint between the two tables.

    I need to change the price of the tickets (tickets.ticket_price) for all events with a specific events.event_name to one value (50).

    events.event_name will be, for example, 'New York'. There are many events in New York, each with a unique event_id.

    So I need to do something along the lines of:

    SET tickets.ticket_price TO '50' WHERE tickets.event_id = events.event_id WHERE events.event_name = 'New York'

    I certainly do not want to alter the structure/relationships of the database in any way - it's used by a Wordpress plug-in which I did not create. From some research, it sounds as if this is a case for an inner JOIN. Does the JOIN command change the database or is it just used for search results? I assume it's the latter, and will not change the database structure.

    Any suggestions would be greatly appreciated!

    - Paul

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    ...and have you trtied that SQL statement..
    and if so did it do waht you expected
    if it di, then jobsagoodun
    if it didn't what happened (what results did you get, or not get as the case may be)
    did the statement run
    how many rows were affected.


    hint it wont run as you have used where twice, it only needs to be ince


    yes you can (and probably should use a join). the general syntax is going to be something like
    Code:
    UPDATE mytable set mynumericcolumn = 123.45, myStringColumn = 'blah', mydatecolumn = "2015/04/21 16:45:00"
    JOIN myothertable on MyTable.acolumn = myothertable.bcolumn
    where myothertable.atextcolumn = 'SQL'
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Mar 2014
    Posts
    8

    Lightbulb

    No, I didn't run my first guess as a statement - there was far too much guesswork in its creation!

    But I'm getting there. I've managed to SELECT all the required data; I just need to replace it now. This is what I made (along the same lines as your suggestion - thank you):

    Code:
    SELECT tickets.ticket_price, events.event_name 
    FROM tickets JOIN events ON tickets.event_id = events.event_id 
    WHERE events.event_name = 'New York'
    ...and it spat out a list of the event names and the corresponding ticket prices. Nice.

    But I need to somehow combine this with a REPLACE. Would this (below) maybe work? If I ran it immediately after the SELECT command, would it work on those found results? Or would MySQL just see it as a completely new command?

    Code:
    SET tickets.ticket_price = REPLACE(tickets.ticket_price, '50' , '40')
    ...or would I be able to combine them like this:

    Code:
    SET tickets.ticket_price, events.event_name 
    FROM tickets JOIN events ON tickets.event_id = events.event_id 
    WHERE events.event_name = 'New York'
     = REPLACE(tickets.ticket_price, '50' , '40')
    What I'm actually trying to do is remove the VAT (a 20% tax) from the price of MOST of the tickets, so what I'd really like to do is something like this:

    tickets.ticket_price = tickets.ticket_price / 1.2

    Sorry for all the questions - if it was a database I'd created myself, I'd just go ahead and run the commands and live with the consequences. But as I mentioned, it's part of a WordPress site and I'd rather like to not break it!

    - Paul

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Ok so if you have proved the JOIN is right, and you are getting ONLY the rows you'd expect then its time to replace the SELECT with an UPDATE clause.

    Effectively the JOIN joons together two or more tables using a piece of data that us ommon between each pair of tables in the join. So thats required to match the event id in events with (what happens to have the same namedcolumn) in tickets

    The WHERE clause defines what subset of the data you require. Omitting the WHERE clause means retrieve all rows. Your where clause can comorise one ir more terms. Eg
    Code:
    WHERE event_name in ('new york', 'washington')
    ...or
    Code:
    WHERE event_name = 'new york' and year (event_date = '2015/04/21")
    So instead of the select column list if what you want to do is correct the price tgen the UPDATE clause is something like
    Code:
    Update tickets set price = price / 1.2
    Join..
    where..
    But to test thus use a select till you are certain the logic is right. Eg
    select event_name, price as oldprice, price /1.2 as exvat
    JOIN......
    WHERE....
    Last edited by healdem; 04-21-15 at 19:40.
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Mar 2014
    Posts
    8

    Question

    Thanks for those suggestions.

    I'm still having trouble trying to make the link between the found results and the UPDATE / SET. I tried this:
    Code:
    UPDATE tickets
    SET ticket_price = ticket_price / 1.2
    JOIN events ON tickets.event_id = events.event_id
    WHERE events.event_name =  'New York'
    AND tickets.ticket_price =  '36'
    ...but I got an error on line 3, the JOIN stuff.

    It seems fairly easy to do an UPDATE, SET, WHERE, but how do I implement the JOIN part?

    Is there a way I can do this as two steps; first SELECT the tickets I need (which works OK), then perform a SET on just those found results?

    Thanks again for your help on this!

    - Paul

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    What error message do you get?
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Mar 2014
    Posts
    8

    Unhappy

    #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'JOIN wp_em_events ON wp_em_tickets.event_id = wp_em_events.event_id WHERE wp_em' at line 3

    You might notice that the tables mentioned in that error are all prefixed with wp_em_. I stripped out the wp_em_ from my earlier posts just to make it all a bit shorter and clearer.
    Last edited by hellopaul; 04-22-15 at 12:45.

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so if the join was working as reported in post #3 and isn't now, what have you changed since then


    presumably youve checked for typos, checked the table name and column names are correctly spelled

    checked other examples elsewhere
    https://www.google.co.uk/webhp?sourc...update+example

    can we see the full SQL statement, historically the error message in MySQL indentifies at what point the SQL parser threw in the towel, so it could be its the bit immediately BEFORE the JOIN which is causing the problem
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Mar 2014
    Posts
    8
    This is the statement from post 3, which works perfectly to produce a list of ticket prices and the event name:

    Code:
    SELECT wp_em_tickets.ticket_price, wp_em_events.event_name FROM wp_em_tickets
    JOIN wp_em_events ON wp_em_tickets.event_id = wp_em_events.event_id
    WHERE wp_em_events.event_name = 'Lewisham - Telegraph Hill' AND wp_em_tickets.ticket_price =  '36'
    ...and this is the statement that does not work:

    Code:
    UPDATE wp_em_tickets
    SET ticket_price = ticket_price / 1.2
    JOIN wp_em_events ON wp_em_tickets.event_id = wp_em_events.event_id
    WHERE wp_em_events.event_name =  'Lewisham - Telegraph Hill'
    AND wp_em_tickets.ticket_price =  '36'

  10. #10
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    the SQL looks fine
    as we don't know your table & column names we have to assume they are right.

    just wondering if you have left a trailing space at the end of each line to make certain the query parser doesn't try to merge lines together

    you may need to use LEFT JOIN, rather than JOIN, although JOIN by itself should be fine

    dadft question
    what datatype is ticket_price?
    if as your where clause suggests its text then that is almost certainly your problem
    numeric literals do not need to be delimited, but they shoudl be of the same type / format or capable of being coerced to that type
    string / text should be delimited by '
    dates should be in ISO date format
    I'd rather be riding on the Tiger 800 or the Norton

  11. #11
    Join Date
    Mar 2014
    Posts
    8
    as we don't know your table & column names we have to assume they are right.
    Yes, they are all correct, because they're copied from the successful SELECT statement. The only new lines of code are the first two, which do not generate any errors.

    just wondering if you have left a trailing space at the end of each line to make certain the query parser doesn't try to merge lines together
    Yes, I just checked this, and the spaces are there.

    you may need to use LEFT JOIN, rather than JOIN, although JOIN by itself should be fine
    I just tried that, and it made no difference.

    what datatype is ticket_price?
    DECIMAL. I also took off the ' ' from the '36' in the last line, and the SELECT command worked just as well (it would presumably fail if ticket_price was a text string, because '36' does not equal 36).

    To my untrained eye, the command, going from UPDATE...SET to JOIN does not really make sense, whereas most SQL commands are readable as a kind-of-sentence.

  12. #12
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Sybase and Microsoft support the JOIN syntax in the WHERE clause, but I can't think of another SQL dialect that supports it.

    In MySQL, you'll need to use the ISO standard syntax, something like:
    Code:
    UPDATE events
       SET ticket_price = 50
       WHERE  EXISTS (SELECT 1
          FROM tickets
          JOIN events AS z
             ON tickets.event_id = z.event_id 
          WHERE z.event_name = 'New York'
             AND events.event_id = z.event_id
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  13. #13
    Join Date
    Mar 2014
    Posts
    8

    Angry

    Thanks for that suggestion, Pat. I tried this:

    Code:
    UPDATE wp_em_tickets 
       SET ticket_price = 500 
       WHERE  EXISTS (SELECT 1 
          FROM wp_em_tickets 
          JOIN wp_em_events 
             ON wp_em_tickets.event_id = wp_em_events.event_id 
          WHERE wp_em_events.event_name = 'Eltham Market' 
             AND wp_em_tickets.event_id = wp_em_events.event_id 
                      AND wp_em_tickets.ticket_price = 40 )
    ...but got an error: #1093 - You can't specify target table 'wp_em_tickets' for update in FROM clause
    Why on earth not?

    I also tried changing the (SELECT 1 to (SELECT *, but it made no difference. I also tried just displaying the desired tickets using:
    Code:
    ( SELECT wp_em_tickets.event_id  
          FROM wp_em_tickets 
          JOIN wp_em_events 
             ON wp_em_tickets.event_id = wp_em_events.event_id 
          WHERE wp_em_events.event_name = 'Eltham Market' 
             AND wp_em_tickets.event_id = wp_em_events.event_id
    AND wp_em_tickets.ticket_price = 40 )
    ...and that worked OK, giving me a list of event_ids at Eltham Market with a ticket_price of 40. So that's cool - we can now go to the wp_em_tickets table and set ticket_price to 500 for all event_ids that match.

    So I added this (in blue) to the start of that same (in green) command:
    Code:
    UPDATE wp_em_tickets 
       SET ticket_price = 500 
       WHERE wp_em_tickets.event_id =
    ( SELECT wp_em_tickets.event_id  
          FROM wp_em_tickets 
          JOIN wp_em_events 
             ON wp_em_tickets.event_id = wp_em_events.event_id 
          WHERE wp_em_events.event_name = 'Eltham Market' 
             AND wp_em_tickets.event_id = wp_em_events.event_id
    AND wp_em_tickets.ticket_price = 40 )
    ...so we'd UPDATE all the tickets where the event_id matches the result of that command. But I got the error #1242 - Subquery returns more than 1 row. Yes, I know. It returned more than one row, which is good. Now update those values. Go on, stupid MySQL, just bloody do it!!

    I think it's time to give up. I have now manually updated all the ticket prices, because spending a couple of hours or so tediously entering a load of data was far, far, quicker and more reliable than doing it the MySQL way. Much more dull, but much faster. I thought that this would be a very simple operation, but I forgot how unintuitive and frustrating it is to use SQL commands. It feels like I'm very close, but at every turn, MySQL is working against me. What an awful piece of programming this thing really is.

    But back to one final question - is it not possible to find a set of data (using SELECT...WHERE...) and then do some kind of operation (e.g. replace) on that previously found data? It seems crazy to attempt to clumsily smoosh everything into one giant, unwieldy command.

  14. #14
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    How about?

    Code:
    UPDATE wp_em_tickets 
       SET ticket_price = 500 
       WHERE  EXISTS (SELECT 1 
          FROM wp_em_events
          WHERE wp_em_events.event_name = 'Eltham Market' 
             AND wp_em_tickets.event_id = wp_em_events.event_id 
                      AND wp_em_tickets.ticket_price = 40 )
    Dave

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •