Professional Web Applications Themes

UPDATE with inner join - Microsoft SQL / MS SQL Server

Does anybody know why inner join doesn't work? UPDATE olapSkladisce INNER JOIN carinskoTest ON (olapSkladisce.idDrzave = carinskoTest.idDrzave) AND (olapSkladisce.idSkladisca = carinskoTest.idSkladisca) SET olapSkladisce.nazivSkladisca=[carinskoTest].[nazivSkladisca], olapSkladisce.okladisca=[carinskoTest].[okladisca], olapSkladisce.namenSkladisca=[carinskoTest].[namenSkladisca] thank you, Simon...

  1. #1

    Default UPDATE with inner join

    Does anybody know why inner join doesn't work?

    UPDATE olapSkladisce
    INNER JOIN
    carinskoTest
    ON (olapSkladisce.idDrzave = carinskoTest.idDrzave) AND
    (olapSkladisce.idSkladisca = carinskoTest.idSkladisca)
    SET olapSkladisce.nazivSkladisca=[carinskoTest].[nazivSkladisca],
    olapSkladisce.okladisca=[carinskoTest].[okladisca],
    olapSkladisce.namenSkladisca=[carinskoTest].[namenSkladisca]

    thank you,
    Simon


    Simon Guest

  2. #2

    Default Re: UPDATE with inner join

    UPDATE a set nazivSkladisca=b.[nazivSkladisca],
    okladisca=b.[okladisca],
    namenSkladisca=b.[namenSkladisca]
    from olapSkladisce a
    INNER JOIN
    carinskoTest b
    ON (a.idDrzave = b.idDrzave) AND
    (a.idSkladisca = b.idSkladisca)

    --
    -Vishal
    "Simon" <simon.zupanstud-moderna.si> wrote in message
    news:3f05953c$1news.s5.net...
    > Does anybody know why inner join doesn't work?
    >
    > UPDATE olapSkladisce
    > INNER JOIN
    > carinskoTest
    > ON (olapSkladisce.idDrzave = carinskoTest.idDrzave) AND
    > (olapSkladisce.idSkladisca = carinskoTest.idSkladisca)
    > SET olapSkladisce.nazivSkladisca=[carinskoTest].[nazivSkladisca],
    > olapSkladisce.okladisca=[carinskoTest].[okladisca],
    > olapSkladisce.namenSkladisca=[carinskoTest].[namenSkladisca]
    >
    > thank you,
    > Simon
    >
    >

    Vishal Parkar Guest

  3. #3

    Default Re: UPDATE with inner join

    Try it the ANSI way.

    UPDATE olapSkladisce
    SET nazivSkladisca =
    (SELECT nazivSkladisca
    FROM carinskoTest
    WHERE idDrzave = olapSkladisce.idDrzave
    AND idSkladisca = olapSkladisce.idSkladisca),
    okladisca =
    (SELECT okladisca
    FROM carinskoTest
    WHERE idDrzave = olapSkladisce.idDrzave
    AND idSkladisca = olapSkladisce.idSkladisca),
    namenSkladisca =
    (SELECT namenSkladisca
    FROM carinskoTest
    WHERE idDrzave = olapSkladisce.idDrzave
    AND idSkladisca = olapSkladisce.idSkladisca)
    WHERE EXISTS
    (SELECT *
    FROM carinskoTest
    WHERE idDrzave = olapSkladisce.idDrzave
    AND idSkladisca = olapSkladisce.idSkladisca)

    The MS-proprietary JOIN syntax is just too peculiar, problematic and
    sometimes downright dangerous. I generally avoid it when I can.

    --
    David Portas
    ------------
    Please reply only to the newsgroup
    --



    David Portas Guest

  4. #4

    Default Re: UPDATE with inner join

    Try:

    insert into olapSkladisce (col1,col2)
    select col1,col2
    from carinskoTest a
    where not exists
    (select 1 from olapSkladisce b
    where a.idDrzave = b.idDrzave AND
    a.idSkladisca = b.idSkladisca)

    --
    -Vishal
    "Simon" <simon.zupanstud-moderna.si> wrote in message
    news:3f059cea$1news.s5.net...
    > Thank you.
    >
    > Do you know the same statement - just for INSERT this time
    > (INSERT the new rows from carinskoTest b INTO olapSkladisce a)
    >
    > Simon
    >
    > "Vishal Parkar" <vgparkarhotmail.com> wrote in message
    > news:uMkGe3jQDHA.2424tk2msftngp13.phx.gbl...
    > > UPDATE a set nazivSkladisca=b.[nazivSkladisca],
    > > okladisca=b.[okladisca],
    > > namenSkladisca=b.[namenSkladisca]
    > > from olapSkladisce a
    > > INNER JOIN
    > > carinskoTest b
    > > ON (a.idDrzave = b.idDrzave) AND
    > > (a.idSkladisca = b.idSkladisca)
    > >
    > > --
    > > -Vishal
    > > "Simon" <simon.zupanstud-moderna.si> wrote in message
    > > news:3f05953c$1news.s5.net...
    > > > Does anybody know why inner join doesn't work?
    > > >
    > > > UPDATE olapSkladisce
    > > > INNER JOIN
    > > > carinskoTest
    > > > ON (olapSkladisce.idDrzave = carinskoTest.idDrzave) AND
    > > > (olapSkladisce.idSkladisca = carinskoTest.idSkladisca)
    > > > SET olapSkladisce.nazivSkladisca=[carinskoTest].[nazivSkladisca],
    > > > olapSkladisce.okladisca=[carinskoTest].[okladisca],
    > > > olapSkladisce.namenSkladisca=[carinskoTest].[namenSkladisca]
    > > >
    > > > thank you,
    > > > Simon
    > > >
    > > >
    > >
    > >
    >
    >

    Vishal Parkar Guest

Similar Threads

  1. Is left-join faster then inner join?
    By howachen@gmail.com in forum MySQL
    Replies: 5
    Last Post: March 5th, 07:11 AM
  2. FMS Join two flv...
    By Luiz Alberto in forum Macromedia Flash Flashcom
    Replies: 0
    Last Post: November 23rd, 02:51 PM
  3. Is self-join appropriate?
    By Michal Stankoviansky in forum MySQL
    Replies: 6
    Last Post: January 3rd, 08:42 PM
  4. trying to update a table after making a join select query on two tables
    By rob merritt in forum ASP.NET Data Grid Control
    Replies: 0
    Last Post: March 1st, 10:33 PM
  5. How to Join...
    By CD in forum Microsoft SQL / MS SQL Server
    Replies: 1
    Last Post: July 10th, 06:26 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