Results 1 to 8 of 8
  1. #1
    Join Date
    May 2014
    Posts
    98

    Unanswered: Merge query not working

    MERGE UserT AS target
    USING SUP/TLupdatedListQuery AS source ON target.Username = source.SUP/TLupdatedListQuery
    AND target.Password = source.PERSON T-ID
    WHEN NOT MATCHED BY TARGET THEN
    INSERT (Field1, PERSON T-ID) VALUES (source.Field1, source.PERSON T-ID)
    WHEN NOT MATCHED BY SOURCE THEN
    DELETE;

    I am using this query and it's not working

    What I am trying to do is merge 2 tables
    I have Table 1 which has old records of employees their names and ID number
    and as employee change in the company so new list comes Table 2 is created I ran a query to pull employee name and ID to merge in table 1
    so some employee record remain same however some change so i need to delete few in old table and add new ones

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    its not working means?
    what error message are you getting?

    what data stroage engine are you using
    ..JET
    ...SQL server
    merge is valid in SQL server, its not valid elsehwere (AFAIK)


    duplicating tables is a bad, bad idea.. it causes this sort of grief
    what you could do, assuming you are using JET is run two querieis
    A)update table1 with the values from table2 where there is a match on some common element(s)
    B) insert new rows into table1 where there is NO match on some common element(s)
    ..arguably you might also want to ruin another query which deleted redundant data
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    May 2014
    Posts
    98
    I am using Access Database and want to do this query of replacing old record and add new records However not all old records will be wiped.


    Quote Originally Posted by healdem View Post
    its not working means?
    what error message are you getting?

    what data stroage engine are you using
    ..JET
    ...SQL server
    merge is valid in SQL server, its not valid elsehwere (AFAIK)


    duplicating tables is a bad, bad idea.. it causes this sort of grief
    what you could do, assuming you are using JET is run two querieis
    A)update table1 with the values from table2 where there is a match on some common element(s)
    B) insert new rows into table1 where there is NO match on some common element(s)
    ..arguably you might also want to ruin another query which deleted redundant data

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    So are you using SQL Server to store you data, if so the merge construct is supported but not using the syntax you are have.
    If you are using JET (and if you dont know the difference then almost certainly you are using JET) then as far as I know it doesnt have anything remotely like merge.
    As said before your options are to run a set of queries
    First run an UPDATE query
    Then run an INSERT INTO query
    Then optionally run a DELETE query.

    INSERT INTO atable ( my, column, list) select some, columns, here from btable left join atable on btable.bcolumn = atable.acolumn
    Where isnull (atable.column ) = true
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    May 2014
    Posts
    98
    Query works however I don't understand the Where isnull (atable.column ) = true Part. Can you educate me what does the query do. As I wanted Some old employee names who left the company to be deleted from table A and add the new employees name from table B. However table B also have names of old employees.

    I am so confused. But I know we are on right path

    Quote Originally Posted by healdem View Post
    So are you using SQL Server to store you data, if so the merge construct is supported but not using the syntax you are have.
    If you are using JET (and if you dont know the difference then almost certainly you are using JET) then as far as I know it doesnt have anything remotely like merge.
    As said before your options are to run a set of queries
    First run an UPDATE query
    Then run an INSERT INTO query
    Then optionally run a DELETE query.

    INSERT INTO atable ( my, column, list) select some, columns, here from btable left join atable on btable.bcolumn = atable.acolumn
    Where isnull (atable.column ) = true

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so if your query works then why ask here?

    ...to break down that query
    Code:
    INSERT INTO atable
    ' insert rows into a table called atable

    Code:
    ( my, column, list)
    'defines the columns to be populated

    Code:
     select
    'this query uses a subselect to select rows from another table

    Code:
    some, columns, here
    'defiens what columns are to be selected from this table, they MUJST be the same order as the columns to be inserted (and same datatype

    Code:
     from btable
    'specifies which table the rows are coming from

    Code:
     left join  atable on
    'defines how two tables are related, google what a left join is. but a quick & dirty definition is:-
    ' a left join means extract all rows fromthe table on the LEFT hand side of the join (btable) and whatever rows match in the table on the RIGHT hand side of the join, if there is no match return null for the columns on the RIGHT hand side

    Code:
    btable.bcolumn = atable.acolumn
    'defines a realtionship between the two tables

    Code:
    Where isnull (atable.column ) = true
    'if the value of the RIGHT hand side of the join is NULL it means there is no record in atable for that row so an insert will work without violating constraints
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    May 2014
    Posts
    98
    Thank you very much Healdem You are the best....

    Can you also tell me the delete query for the same scenario.....

    Quote Originally Posted by healdem View Post
    so if your query works then why ask here?
    ...to break down that query
    Code:
    INSERT INTO atable
    ' insert rows into a table called atable

    Code:
    ( my, column, list)
    'defines the columns to be populated

    Code:
     select
    'this query uses a subselect to select rows from another table

    Code:
    some, columns, here
    'defiens what columns are to be selected from this table, they MUJST be the same order as the columns to be inserted (and same datatype

    Code:
     from btable
    'specifies which table the rows are coming from

    Code:
     left join  atable on
    'defines how two tables are related, google what a left join is. but a quick & dirty definition is:-
    ' a left join means extract all rows fromthe table on the LEFT hand side of the join (btable) and whatever rows match in the table on the RIGHT hand side of the join, if there is no match return null for the columns on the RIGHT hand side

    Code:
    btable.bcolumn = atable.acolumn
    'defines a realtionship between the two tables

    Code:
    Where isnull (atable.column ) = true
    'if the value of the RIGHT hand side of the join is NULL it means there is no record in atable for that row so an insert will work without violating constraints

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    essentially the delete query uses the same logical approach, a left JOIN which returns all values form the tbale on the LEFT side of the join, and a those rows from the table on the right side of the join where there is a match on the specified column(s) if there is no match then return a null value.

    so your test is for a null value.

    so if you want to delete rows in table A that are not in table B then.....
    what do you think you need to do.
    hint, if you are struggling then use the find unmatched query wizard
    test it out on a select query before converting it into a delete, just in case you screw something up
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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