Results 1 to 15 of 15

Thread: How do we change our update statment? we want to add rows from NEWschoolmark table th

  1. #1

    Thread Starter
    Member
    Join Date
    Jun 2019
    Posts
    54

    How do we change our update statment? we want to add rows from NEWschoolmark table th

    How do we change our update statment? we want to add rows from NEWschoolmark table that does not existed in schoolmark table
    UPDATE schoolmark INNER JOIN NEWschoolmark ON schoolmark.field1 = NEWschoolmark.field1 SET schoolmark.field3 = [NEWschoolmark].field3;

  2. #2
    A SQL Server fool GaryMazzone's Avatar
    Join Date
    Aug 2005
    Location
    Dover,NH
    Posts
    7,495

    Re: How do we change our update statment? we want to add rows from NEWschoolmark tabl

    You can't update rows that do not exsist in the table already....
    You need to do an insert to the table where not already in place...
    Sometimes the Programmer
    Sometimes the DBA

    Mazz1

  3. #3

    Thread Starter
    Member
    Join Date
    Jun 2019
    Posts
    54

    Re: How do we change our update statment? we want to add rows from NEWschoolmark tabl

    how do i make insert with descreat?

  4. #4
    A SQL Server fool GaryMazzone's Avatar
    Join Date
    Aug 2005
    Location
    Dover,NH
    Posts
    7,495

    Re: How do we change our update statment? we want to add rows from NEWschoolmark tabl

    We will need more info than you have supplied here... What is the database? Where do the tables exist? Does your DB support the MERGE statement? As say more info
    Sometimes the Programmer
    Sometimes the DBA

    Mazz1

  5. #5

    Thread Starter
    Member
    Join Date
    Jun 2019
    Posts
    54

    Re: How do we change our update statment? we want to add rows from NEWschoolmark tabl

    ms access 2016 in database yes support merge

  6. #6

    Thread Starter
    Member
    Join Date
    Jun 2019
    Posts
    54

    Re: How do we change our update statment? we want to add rows from NEWschoolmark tabl

    what is insert we can make for table schoolmark insert values from Newschoolmark that absent in table schoolmark ?
    booth table have the same three fields fields1 fields2 fields3 and key is field1

  7. #7

    Thread Starter
    Member
    Join Date
    Jun 2019
    Posts
    54

    Re: How do we change our update statment? we want to add rows from NEWschoolmark tabl

    Why don't work
    UPDATE schoolmark RIGHT JOIN NEWschoolmark ON schoolmark.field1 = NEWschoolmark.field1 SET schoolmark.field3 = [NEWschoolmark].field3;

  8. #8
    A SQL Server fool GaryMazzone's Avatar
    Join Date
    Aug 2005
    Location
    Dover,NH
    Posts
    7,495

    Re: How do we change our update statment? we want to add rows from NEWschoolmark tabl

    If a record exists in table 2 and not in table 1 there is no way to update table 1 it is not there..... If it exists in both table 2 and table 1 then you can update. If it exists in table 1 and not table 2 you can't update either right.....

    So
    Code:
    Update t1 SET 
        t1.field1 = t2.field1,
        t1.field2 = t2.field2,
        t1.field3 = t2.field3
    FROM table1 t1
    INNER JOIN table2 t2
       on t1.somefield = t2.somefield;
    That updates where the record is in both tables
    Now we want to insert the records that exist in table2 but are not in table 1
    Code:
    INSERT INTO table1 (field1,field2,field3)
    Select 
       field1,
       field2,
       field3
    From table2 t2
    WHERE t2.somecomonfield NOT IN (Select somecomonfield from table1)
    A merge statement would be used to do all of that in one statement.... I would not have you try that since I believe it is beyond your ability yet (it is also not very efficient most of the time)
    Sometimes the Programmer
    Sometimes the DBA

    Mazz1

  9. #9

    Thread Starter
    Member
    Join Date
    Jun 2019
    Posts
    54

    Re: How do we change our update statment? we want to add rows from NEWschoolmark tabl

    is it right?

    INSERT INTO table1 (field1,field2,field3)
    Select
    field1,
    field2,
    field3
    From table2 t2
    WHERE t2.field1 NOT IN (Select field1 from table1)


    key is field1 and consist of unic article number?
    what is somecomnfield is it field3 that consist digital value?

    table 1
    field1 field2 field3
    AC3Z2510624A age 6
    AC3Z2521410A age 8
    AC3Z2521411A age 5,5
    AC3Z2552A age 5,5
    AC3Z2553A age 5,5
    AC3Z25611B08AA age 1,5
    AC3Z25611B08AB age 1,5
    AC3Z25611B09AA age 1,5
    AC3Z25611B09AB age 1,5

    table2
    field1 field2 field3
    AC3Z2521410A age 8
    AC3Z2521411A age 5,5
    AC3Z2B120A age 8
    AC3Z2B120B age 10
    AC3Z2B121A age 8
    AC3Z2B121B age 10
    AE5Z16005A age 6
    AE5Z16006A age 6
    AE6Z16005A age 6,5
    AE6Z16006A age 6,5

  10. #10

    Thread Starter
    Member
    Join Date
    Jun 2019
    Posts
    54

    Re: How do we change our update statment? we want to add rows from NEWschoolmark tabl

    why we can not use right join in access like in Oracle finance istead of insert?
    UPDATE schoolmark RIGHT JOIN NEWschoolmark ON schoolmark.field1 = NEWschoolmark.field1 SET schoolmark.field3 = [NEWschoolmark].field3;

  11. #11

    Thread Starter
    Member
    Join Date
    Jun 2019
    Posts
    54

    Re: How do we change our update statment? we want to add rows from NEWschoolmark tabl

    why does the insert so slow can we remake with visual basic the same?

  12. #12
    A SQL Server fool GaryMazzone's Avatar
    Join Date
    Aug 2005
    Location
    Dover,NH
    Posts
    7,495

    Re: How do we change our update statment? we want to add rows from NEWschoolmark tabl

    Another way

    Code:
    INSERT INTO TABLE1 (field1,field2,field3)
    SELECT 
       t2.field1
       ,t2.field2
       ,t2.field3
    FROM table2 t2
    LEFT OUTER JOIN table1 t1
       ON t2.field1 = t1.feild1
    WHERE t1.field1 IS NULL;
    Sometimes the Programmer
    Sometimes the DBA

    Mazz1

  13. #13

    Thread Starter
    Member
    Join Date
    Jun 2019
    Posts
    54

    Re: How do we change our update statment? we want to add rows from NEWschoolmark tabl

    where do i read about left outer join
    and how do i make it with visual basic for 20000 records very slow
    Last edited by daveramsey; Apr 27th, 2021 at 09:40 AM.

  14. #14
    A SQL Server fool GaryMazzone's Avatar
    Join Date
    Aug 2005
    Location
    Dover,NH
    Posts
    7,495

    Re: How do we change our update statment? we want to add rows from NEWschoolmark tabl

    I have no idea why yours run slowly I can do that over tables with 10,000,000 rows and get good speed.... If you are running MS Access that is not the fastest database there is. The second one down on the left side is what I showed
    Sometimes the Programmer
    Sometimes the DBA

    Mazz1

  15. #15
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,531

    Re: How do we change our update statment? we want to add rows from NEWschoolmark tabl

    Quote Originally Posted by daveramsey View Post
    where do i read about left outer join
    and how do i make it with visual basic for 20000 records very slow
    Inner joins return records that exist in both the left and right tables. All other types of joins are considered OUTER... With Left and Right, you just don't need to specify the OUTER as it is implied with the LEFT and RIGHT. The only time OUTER is needed is when using the FULL OUTER construct.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

Posting Permissions

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



Click Here to Expand Forum to Full Width