Professional Web Applications Themes

how do you do "if record doesn't exist create otherwise update" in one SQL statement? - ASP Database

how do you do "if record doesn't exist create otherwise update" in one SQL statement? -Mike...

  1. #1

    Default how do you do "if record doesn't exist create otherwise update" in one SQL statement?

    how do you do "if record doesn't exist create otherwise update" in one
    SQL statement?

    -Mike
    Mike Guest

  2. #2

    Default Re: how do you do "if record doesn't exist create otherwise update" in one SQL statement?

    I don't think you can do this in straight ANSI SQL. If you are using
    something like T-SQL (Transact SQL) or PLSQL then it's possible. What DBMS
    are you using?

    Cheers
    Ken

    "Mike" <com> wrote in message
    news:google.com... 


    Ken Guest

  3. #3

    Default Re: how do you do "if record doesn't exist create otherwise update" in one SQL statement?

    Assuming SQL Server (never a bad idea to tell us which database you're
    using; "SQL" is pretty generic and ambiguous).

    UPDATE table SET ... WHERE ...
    IF ROWCOUNT = 0
    INSERT table(col_list) SELECT val_list

    --
    http://www.aspfaq.com/
    (Reverse address to reply.)




    "Mike" <com> wrote in message
    news:google.com... 


    Aaron Guest

  4. #4

    Default Re: how do you do "if record doesn't exist create otherwise update" in one SQL statement?

    "Aaron [SQL Server MVP]" <noraa> wrote in message news:<#t#phx.gbl>... 

    I'm using Access 2000

    Here's my statement:

    UPDATE MiniStatData SET statValue = NULL WHERE statId = 1 IF
    ROWCOUNT = 0 INSERT MiniStatData(miniId, statId, statValue) SELECT
    309, 1, NULL

    I get a "Syntax error (missing operator) in query expression ..."
    error when I try running it. Is it because I am using Access or did I
    write something wrong?

    Thanks,
    Mike
    Mike Guest

  5. #5

    Default Re: how do you do "if record doesn't exist create otherwise update" in one SQL statement?

    > UPDATE MiniStatData SET statValue = NULL WHERE statId = 1 IF 

    Yes, it's because you are using Access. My post said "Assuming SQL
    Server"...

    With Access, you might have to do it this way:

    set rs = conn.execute("SELECT COUNT(*) FROM table WHERE ...")
    if rs(0) > 0 then
    ' run update
    else
    ' run insert
    end if

    Maybe Bob could show us a stored query that does the same thing. I'm not
    too big on VBA or how multi-statement stored queries work, because Access is
    far from my cup 'o' tea.

    --
    http://www.aspfaq.com/
    (Reverse address to reply.)


    Aaron Guest

  6. #6

    Default Re: how do you do "if record doesn't exist create otherwise update" in one SQL statement?

    Aaron [SQL Server MVP] wrote: 
    >
    > Yes, it's because you are using Access. My post said "Assuming SQL
    > Server"...
    >
    > With Access, you might have to do it this way:
    >
    > set rs = conn.execute("SELECT COUNT(*) FROM table WHERE ...")
    > if rs(0) > 0 then
    > ' run update
    > else
    > ' run insert
    > end if
    >
    > Maybe Bob could show us a stored query that does the same thing. I'm
    > not too big on VBA or how multi-statement stored queries work,
    > because Access is far from my cup 'o' tea.[/ref]

    Nope. No such thing as UPSERT.

    In addition, Jet does not support batched queries. You have to send one
    statement at a time.

    Yet another reason to switch to SQL Server if high throughput is needed.

    Bob Barrows
    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.


    Bob Guest

  7. #7

    Default Re: how do you do "if record doesn't exist create otherwise update" in one SQL statement?

    > Nope. No such thing as UPSERT.

    <whine>
    Aw, but Oracle has it (MERGE)!
    </whine>

    ;-) for the humor impaired.

    --
    http://www.aspfaq.com/
    (Reverse address to reply.)


    Aaron Guest

  8. #8

    Default Re: how do you do "if record doesn't exist create otherwise update" in one SQL statement?

    "Mike" <com> wrote in message
    news:google.com... 

    http://groups.google.com/groups?threadm=uOitbZwcDHA.3708%40tk2msftngp13.phx .gbl


    Chris Guest

  9. #9

    Default Re: how do you do "if record doesn't exist create otherwise update" in one SQL statement?

    Chris Hohmann wrote: 
    >
    > http://groups.google.com/groups?threadm=uOitbZwcDHA.3708%40tk2msftngp13.phx .gbl[/ref]

    Wow! I forgot all about that!! Great catch Chris!

    Bob

    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"


    Bob Guest

  10. #10

    Default Re: how do you do "if record doesn't exist create otherwise update" in one SQL statement?

    "Chris Hohmann" <com> wrote in message news:<ujhLkh$phx.gbl>... 
    >
    > http://groups.google.com/groups?threadm=uOitbZwcDHA.3708%40tk2msftngp13.phx .gbl[/ref]

    Does this post mean it is possible to do what I am asking, namely
    insert a record if it doesn't exist or update it if it does? This
    post seems to require a prexisting table with values already updated.

    -Mike
    Mike Guest

  11. #11

    Default Re: how do you do "if record doesn't exist create otherwise update" in one SQL statement?

    "Chris Hohmann" <com> wrote in message news:<ujhLkh$phx.gbl>... 
    >
    > http://groups.google.com/groups?threadm=uOitbZwcDHA.3708%40tk2msftngp13.phx .gbl[/ref]

    Does this post mean it is possible to do what I am asking, namely
    insert a record if it doesn't exist or update it if it does? This
    post seems to require a prexisting table with values already updated.

    -Mike
    Mike Guest

  12. #12

    Default Re: how do you do "if record doesn't exist create otherwise update" in one SQL statement?

    Mike wrote: 
    >>
    >>[/ref][/ref]
    http://groups.google.com/groups?threadm=uOitbZwcDHA.3708%40tk2msftngp13.phx .gbl 

    I've never tiied it, but I suspect a table is required. Maybe a join to a
    subquery that supplies the data will work ...

    Yes, I got it to work. I needed to use a kludge, but here is the query that
    worked:

    UPDATE table1 as t RIGHT JOIN
    (SELECT TOP 1 'test' as lastname, '5' as ssn from Table1) As q
    ON t.SSN= q.SSN
    SET t.lastname=q.lastname,t.SSN=q.SSN

    With Access 97, you would have to use this syntax:

    ... JOIN [subquery]. as q

    The parentheses work in A2000 and higher.

    Bob Barrows
    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.


    Bob Guest

  13. #13

    Default Re: how do you do "if record doesn't exist create otherwise update" in one SQL statement?

    Mike-

    If you "update" data is coming from another table, you can do it in Access
    with one statement:

    UPDATE MyTable
    RIGHT JOIN DataTable
    ON MyTable.Pkey = DataTable.Pkey
    SET MyTable.Field1 = DataTable.Field1, ... , MyTable.FieldN =
    DataTable.FieldN

    If the target table has an Autonumber as a Primary Key, do not include the
    autonumber field in the SET list.

    --
    John Viescas, author
    "Microsoft Office Access 2003 Inside Out"
    "Running Microsoft Access 2000"
    "SQL Queries for Mere Mortals"
    http://www.viescas.com/
    (Microsoft Access MVP since 1993)
    "Mike" <com> wrote in message
    news:google.com... 


    John Guest

  14. #14

    Default Re: how do you do "if record doesn't exist create otherwise update" in one SQL statement?

    John Viescas wrote: [/ref]

    Hey John,
    I haven't seen you hanging around here before.I thought you just did the
    Access groups.

    Just to let you know, we've already steered him toward that solution. In
    fact, I posted a query that did not require a separate table.

    Bob Barrows
    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.


    Bob Guest

  15. #15

    Default Re: how do you do "if record doesn't exist create otherwise update" in one SQL statement?

    Bob-

    Yup, I tend to hang out only in the Access groups, but Mike also sent me an
    email pointing to the thread, so I thought I'd pop in here and answer it
    publicly. Your answer using a TOP 1 subquery is inventive - you should have
    pointed out that you can substitute any table for Table1 in the subquery.
    If you have a dummy 1-row table lying around, you can also avoid the TOP 1
    kludge. (I tend to include one 1-row table in all my apps to UNION a null
    entry in combo box lists.)

    --
    John Viescas, author
    "Microsoft Office Access 2003 Inside Out"
    "Running Microsoft Access 2000"
    "SQL Queries for Mere Mortals"
    http://www.viescas.com/
    (Microsoft Access MVP since 1993)
    "Bob Barrows [MVP]" <SPAMcom> wrote in message
    news:phx.gbl... [/ref]
    >
    > Hey John,
    > I haven't seen you hanging around here before.I thought you just did the
    > Access groups.
    >
    > Just to let you know, we've already steered him toward that solution. In
    > fact, I posted a query that did not require a separate table.
    >
    > Bob Barrows
    > --
    > Microsoft MVP -- ASP/ASP.NET
    > Please reply to the newsgroup. The email account listed in my From
    > header is my spam trap, so I don't check it very often. You will get a
    > quicker response by posting to the newsgroup.
    >
    >[/ref]


    John Guest

  16. #16

    Default Re: how do you do "if record doesn't exist create otherwise update" in one SQL statement?

    John Viescas wrote: 

    Yes, that's not obvious. I should have also pointed out that if Table1 was
    empty, the query would fail. It would probably have been better to use
    MSysObjects for the subquery, since that table should never be empty.
     
    I remember doing that too. I haven't had to do that since moving to SQL
    Server.

    Bob Barrows

    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"


    Bob Guest

  17. #17

    Default Re: how do you do "if record doesn't exist create otherwise update" in one SQL statement?

    "Bob Barrows [MVP]" <SPAMcom> wrote in message
    news:phx.gbl... 
    >
    > Yes, that's not obvious. I should have also pointed out that if Table1 was
    > empty, the query would fail. It would probably have been better to use
    > MSysObjects for the subquery, since that table should never be empty.

    > I remember doing that too. I haven't had to do that since moving to SQL
    > Server.[/ref]

    You can take a cue[sp?] from Oracle and name the table DUAL. The DUAL table
    is also useful for batch inserts/updates. Something like

    INSERT INTO MyTable(MyField1,MyField2,MyField3)
    SELECT 1 AS MyField1, 2 AS MyField2, 3 AS MyField3 FROM DUAL UNION ALL
    SELECT 4 AS MyField1, 5 AS MyField2, 6 AS MyField3 FROM DUAL UNION ALL
    SELECT 7 AS MyField1, 8 AS MyField2, 9 AS MyField3 FROM DUAL UNION ALL
    ..
    ..
    ..


    Chris Guest

  18. #18

    Default Re: how do you do "if record doesn't exist create otherwise update" in one SQL statement?

    "Bob Barrows [MVP]" <SPAMcom> wrote in message news:<unqRe#phx.gbl>... [/ref]
    > http://groups.google.com/groups?threadm=uOitbZwcDHA.3708%40tk2msftngp13.phx .gbl 
    >
    > I've never tiied it, but I suspect a table is required. Maybe a join to a
    > subquery that supplies the data will work ...
    >
    > Yes, I got it to work. I needed to use a kludge, but here is the query that
    > worked:
    >
    > UPDATE table1 as t RIGHT JOIN
    > (SELECT TOP 1 'test' as lastname, '5' as ssn from Table1) As q
    > ON t.SSN= q.SSN
    > SET t.lastname=q.lastname,t.SSN=q.SSN
    >
    > With Access 97, you would have to use this syntax:
    >
    > .. JOIN [subquery]. as q
    >
    > The parentheses work in A2000 and higher.
    >
    > Bob Barrows[/ref]

    Here's your statement adapted for my database:

    UPDATE MiniStatData as t RIGHT JOIN
    (SELECT TOP 1 NULL as statValue, 1 as statId, 313 as miniId from
    MiniStatData) As q ON t.statId = q.statId
    SET t.statValue = q.statValue, t.statId = q.statId, t.miniId =
    q.miniId


    Unfortunately it seems to crash Access 2000 or something else; it
    runs, but then I cannot surf to any other database-driven web page on
    the server after that (even if it uses a different database). I
    checked the database directory after rebooting and there was an .ldb
    file there for the database I had accessed. I deleted the .mdb and
    the .ldb and installed a backup of the .mdb; I was then able to access
    the web pages again. Then I tried moving the backup mdb to another
    directory and restoring the original database from the trashcan. I
    was able to open it in access and it looked fine, but when I tried
    accessing a page using that database, the page froze just like before.
    I closed the browser and opened a new copy- same problem, all the
    database pages, even the ones using different databases, didn't work
    (this time there was no .ldb in the directory). Could it have
    corrupted the Access file?
    Mike Guest

  19. #19

    Default Re: how do you do "if record doesn't exist create otherwise update" in one SQL statement?

    "Bob Barrows [MVP]" <SPAMcom> wrote in message news:<unqRe#phx.gbl>... [/ref]
    > http://groups.google.com/groups?threadm=uOitbZwcDHA.3708%40tk2msftngp13.phx .gbl 
    >
    > I've never tiied it, but I suspect a table is required. Maybe a join to a
    > subquery that supplies the data will work ...
    >
    > Yes, I got it to work. I needed to use a kludge, but here is the query that
    > worked:
    >
    > UPDATE table1 as t RIGHT JOIN
    > (SELECT TOP 1 'test' as lastname, '5' as ssn from Table1) As q
    > ON t.SSN= q.SSN
    > SET t.lastname=q.lastname,t.SSN=q.SSN
    >
    > With Access 97, you would have to use this syntax:
    >
    > .. JOIN [subquery]. as q
    >
    > The parentheses work in A2000 and higher.
    >
    > Bob Barrows[/ref]

    "Bob Barrows [MVP]" <SPAMcom> wrote in message news:<unqRe#phx.gbl>... [/ref]
    > http://groups.google.com/groups?threadm=uOitbZwcDHA.3708%40tk2msftngp13.phx .gbl 
    >
    > I've never tiied it, but I suspect a table is required. Maybe a join to a
    > subquery that supplies the data will work ...
    >
    > Yes, I got it to work. I needed to use a kludge, but here is the query that
    > worked:
    >
    > UPDATE table1 as t RIGHT JOIN
    > (SELECT TOP 1 'test' as lastname, '5' as ssn from Table1) As q
    > ON t.SSN= q.SSN
    > SET t.lastname=q.lastname,t.SSN=q.SSN
    >
    > With Access 97, you would have to use this syntax:
    >
    > .. JOIN [subquery]. as q
    >
    > The parentheses work in A2000 and higher.
    >
    > Bob Barrows[/ref]

    Here's your statement adapted for my database:

    UPDATE MiniStatData as t RIGHT JOIN
    (SELECT TOP 1 NULL as statValue, 1 as statId, 313 as miniId from
    MiniStatData) As q ON t.statId = q.statId
    SET t.statValue = q.statValue, t.statId = q.statId, t.miniId =
    q.miniId


    Unfortunately it seems to crash Access 2000 or something else; it
    runs, but then I cannot surf to any other database-driven web page on
    the server after that (even if it uses a different database). I
    checked the database directory after rebooting and there was an .ldb
    file there for the database I had accessed. I deleted the .mdb and
    the .ldb and installed a backup of the .mdb; I was then able to access
    the web pages again. Then I tried moving the backup mdb to another
    directory and restoring the original database from the trashcan. I
    was able to open it in access and it looked fine, but when I tried
    accessing a page using that database, the page froze just like before.
    I closed the browser and opened a new copy- same problem, all the
    database pages, even the ones using different databases, didn't work
    (this time there was no .ldb in the directory). Could it have
    corrupted the Access file?
    Mike Guest

  20. #20

    Default Re: how do you do "if record doesn't exist create otherwise update" in one SQL statement?

    Chris Hohmann wrote: 
    Is there something special about the name "DUAL"? It seems to me any name
    for this table would work.

    Bob Barrows.

    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"


    Bob Guest

Page 1 of 2 12 LastLast

Similar Threads

  1. "The file you selected does not exist" when the filedefinitely exists?
    By reubidium in forum Macromedia Contribute General Discussion
    Replies: 0
    Last Post: February 7th, 09:47 PM
  2. "file doesn't exist" !?
    By inte@adobeforums.com in forum Adobe Acrobat Macintosh
    Replies: 3
    Last Post: July 20th, 05:09 PM
  3. why is the "Adobe Acrobat 6.0.1 Update" message there at the top? -- the update doesn't work
    By john_cummin@adobeforums.com in forum Adobe Acrobat Macintosh
    Replies: 6
    Last Post: August 5th, 01:51 PM
  4. "Duplicate Name Exist On Network" after suspend or undock
    By Samuel Friedman in forum Windows Networking
    Replies: 0
    Last Post: July 2nd, 01:08 AM
  5. Specifying "do not update" values in "additive" UPDATE sprocs
    By Joel Thornton in forum Microsoft SQL / MS SQL Server
    Replies: 1
    Last Post: June 30th, 10:27 PM

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
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139