SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Guru johnjohn2's Avatar
    Join Date
    Apr 2004
    Location
    here
    Posts
    746
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    proper update sql syntax?

    Can someone tell me if this update query's sql looks ok. I'm thinking it might have some problems.

    UPDATE tbl95view1 INNER JOIN tbl88view1 ON (tbl95view1.col36 = tbl88view1.col27) AND (tbl95view1.col13 = tbl88view1.col23) AND (tbl95view1.col2 = tbl88view1.col2) SET tbl88view1.col20 = tbl95view1.col35
    WHERE (tbl95view1.col13=accountid) AND (tbl95view1.col35>=checkno);

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    once again, the question has failed to specify which database this is for

    UPDATE syntax varies from database to database
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Guru johnjohn2's Avatar
    Join Date
    Apr 2004
    Location
    here
    Posts
    746
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Woops, sorry. Postgresql 7.4

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    okay, hang on a few moments while i go look up the syntax on their web site

    i personally don't use postgresql, but i can read manuals real good
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    assuming accountid and checkno are values passed to the query,
    Code:
    UPDATE tbl88view1 
       SET tbl88view1.col20 = tbl95view1.col35
      FROM tbl95view1 
     WHERE tbl88view1.col27 = tbl95view1.col36
       AND tbl88view1.col23 = tbl95view1.col13 
       AND tbl88view1.col2 = tbl95view1.col2  
       AND tbl95view1.col13 = accountid
       AND tbl95view1.col35 >= checkno
    you know where to look this up on their site, right?
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Guru johnjohn2's Avatar
    Join Date
    Apr 2004
    Location
    here
    Posts
    746
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Here's my final code. It's complaining with an error of:
    ERROR: syntax error at or near "." at character 38

    I'm trying to troubleshoot it at the moment. If anyone has any suggestions please let me know. Thanks.

    PHP Code:
    // begin Recordset
    $recordid66__Recordset4 '-1';
    if (isset(
    $_GET['recordid66'])) {
      
    $recordid66__Recordset4 $_GET['recordid66'];
    }
    $checkno__Recordset4 '-1';
    if (isset(
    $_GET['checkno'])) {
      
    $checkno__Recordset4 $_GET['checkno'];
    }
    $query_Recordset4 sprintf("UPDATE tbl88view1 
       SET tbl88view1.col20 = tbl95view1.col35
      FROM tbl95view1 
     WHERE tbl88view1.col27 = tbl95view1.col36
       AND tbl88view1.col23 = tbl95view1.col13 
       AND tbl88view1.col2 = tbl95view1.col2  
       AND tbl95view1.col13 = %s
       AND tbl95view1.col35 >= %s"
    $recordid66__Recordset4,$checkno__Recordset4);
    $Recordset4 $base2->SelectLimit($query_Recordset4) or die($base2->ErrorMsg());
    // end Recordset 
    These update queries in php can update multiple records all at the same time right? Or am I wrong in assuming this.

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    multiple records, yes

    as for your syntax error, try replacing this --

    UPDATE tbl88view1
    SET tbl88view1.col20 = tbl95view1.col35

    with this --

    UPDATE tbl88view1
    SET col20 = tbl95view1.col35
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Guru johnjohn2's Avatar
    Join Date
    Apr 2004
    Location
    here
    Posts
    746
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Works like a charm!!! I really appreciate the help!


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
  •