SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Wizard geiger's Avatar
    Join Date
    Jul 2001
    Posts
    2,459
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Possible to combine these queries into one?

    Is it possible to, for instance, combine these two queries into one? I have about 7 variables for each of 12 queries. They all essentially do the same thing, but I doubt it's very efficient how it is.

    UPDATE models SET value1=$value1c1 AND value2=$value2c1 WHERE criteria=1
    UPDATE models SET value1=$value1c2 AND value2=$value2c2 WHERE criteria=2

  2. #2
    SitePoint Wizard geiger's Avatar
    Join Date
    Jul 2001
    Posts
    2,459
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Oops! This belongs in the MySQL forum Sorry. Can't move it.

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,016
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    yes, it's possible

    UPDATE models SET value1=$value1c1 AND value2=$value2c1 WHERE criteria IN (1,2)
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Wizard geiger's Avatar
    Join Date
    Jul 2001
    Posts
    2,459
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks. I'll go try it out.

  5. #5
    SitePoint Wizard geiger's Avatar
    Join Date
    Jul 2001
    Posts
    2,459
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I tried adapting that for my use, but it didn't work. It actually threw up an error somewhere else for some reason (syntax near ") even though this query is the problem. Please help fix this?

    PHP Code:
    $updateSQL=mysql_query("UPDATE models
     SET cars=
    $countaccord, median=$accordstats[50], mean=$accordmean, q1=$accordstats[25], q3=$accordstats[75], error=$accordse, sd=$accordsd
     AND cars=
    $countciviccvt, median=$civiccvtstats[50], mean=$civiccvtmean, q1=$civiccvtstats[25], q3=$civiccvtstats[75], error=$civiccvtse, sd=$civiccvtsd
     AND cars=
    $countcivicmanual, median=$civicmanualstats[50], mean=$civicmanualmean, q1=$civicmanualstats[25], q3=$civicmanualstats[75], error=$civicmanualse, sd=$civicmanualsd
     AND cars=
    $countescape2wd, median=$escape2wdstats[50], mean=$escape2wdmean, q1=$escape2wdstats[25], q3=$escape2wdstats[75], error=$escape2wdse, sd=$escape2wdsd
     AND cars=
    $countescape4wd, median=$escape4wdstats[50], mean=$escape4wdmean, q1=$escape4wdstats[25], q3=$escape4wdstats[75], error=$escape4wdse, sd=$escape4wdsd
     AND cars=
    $counthighlander2wd, median=$highlander2wdstats[50], mean=$highlander2wdmean, q1=$highlander2wdstats[25], q3=$highlander2wdstats[75], error=$highlander2wdse, sd=$highlander2wdsd
     AND cars=
    $counthighlander4wd, median=$highlander4wdstats[50], mean=$highlander4wdmean, q1=$highlander4wdstats[25], q3=$highlander4wdstats[75], error=$highlander4wdse, sd=$highlander4wdsd
     AND cars=
    $countinsightcvt, median=$insightcvtstats[50], mean=$insightcvtmean, q1=$insightcvtstats[25], q3=$insightcvtstats[75], error=$insightcvtse, sd=$insightcvtsd
     AND cars=
    $countinsightmanual, median=$insightmanualstats[50], mean=$insightmanualmean, q1=$insightmanualstats[25], q3=$insightmanualstats[75], error=$insightmanualse, sd=$insightmanualsd
     AND cars=
    $countpriusths, median=$priusthsstats[50], mean=$priusthsmean, q1=$priusthsstats[25], q3=$priusthsstats[75], error=$priusthsse, sd=$priusthssd
     AND cars=
    $countpriushsd, median=$priushsdstats[50], mean=$priushsdmean, q1=$priushsdstats[25], q3=$priushsdstats[75], error=$priushsdse, sd=$priushsdsd
     AND cars=
    $countrx, median=$rxstats[50], mean=$rxmean, q1=$rxstats[25], q3=$rxstats[75], error=$rxse, sd=$rxsd
     WHERE shortname IN ('accord', 'civiccvt', 'civicmanual', 'escape2wd', 'escape4wd', 'highlander2wd', 'highlander4wd', 'insightcvt', 'insightmanual', 'priusths', 'priushsd', 'rx'"
    ) or die(mysql_error()); 

  6. #6
    SitePoint Wizard geiger's Avatar
    Join Date
    Jul 2001
    Posts
    2,459
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Just noticed I forgot a ) in that example. That's one huge ugly thing, too, eh?

  7. #7
    SitePoint Wizard geiger's Avatar
    Join Date
    Jul 2001
    Posts
    2,459
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Okay, problem. It's using the values of the last AND for every single row. The "rx" values are being placed everywhere. Any idea what's wrong?

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,016
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    the ANDs are wrong, sorry, i should've spotted that in your first fake example

    but what you are trying to do is set 7 columns to different values depending on which of 12 values the shortname is

    (i also did not notice that in your fake example, you were setting the columns to different values)

    you could do this with one UPDATE, but you would have to write 12 CASE expressions

    my advice: use 12 different UPDATE statements
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Wizard geiger's Avatar
    Join Date
    Jul 2001
    Posts
    2,459
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Okay. Can do. I already have those written, but I though it'd be much more intensive. This is for a big car mileage database. Whenever there's a change, a bunch of processes are run to update the statistics. More queries than I'd like, but it's either this or make the viewing of the data undergo calculating processes. I'd rather just make viewers pull an already-stored value from the database. Did that make any sense?


Bookmarks

Posting Permissions

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