Results 1 to 8 of 8
  1. #1
    Join Date
    Jul 2007
    Posts
    5

    Question Unanswered: Update table from subselect openquery (SQL2K5)

    hi all.
    I'm needing some help on updating whole column of a table (T1) according to a 2nd table T2(wich is a subselect from an openquery). I need to do this update every time a new record is added on T1.
    Here are my table columns (& keys):

    T1:
    ID,T1_Col1, T1_Col2, T1_Col3, T1_Col4, T1_Col4, T1_Col5

    T2: (wich is the return from the openquery)
    T2_Col1, T2_Col2
    this one returns more records than exists on T1

    Here's what i need to do:

    Set the T1_Col4 = T2_Col2
    Where T1_Col1= T2_Col1
    Every time an insert on T1 occurs (this should be part of a trigger)

    My problems are with the use of the subselect from the openquery.

    Can someboby help me on this??, I'm Stuck.

    Regards,

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    I don't know what you mean with "subselect from the openquery". Could you explain this phrase a bit?
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    I don't know what you mean with "subselect from the openquery". Could you explain this phrase a bit?
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  4. #4
    Join Date
    Jul 2007
    Posts
    5
    Sorry about my bad english.

    What i mean is:

    This is my Select from a linked server.
    SELECT A_Tag, A_Desc FROM openquery(RealTFIX,
    'SELECT DISTINCT (ALCOBACA.A_TAG) ,ALCOBACA.A_Desc
    FROM ALCOBACA
    WHERE (ALCOBACA.A_NAME <> ''AR'') AND (ALCOBACA.A_NAME <> ''DR'') AND (ALCOBACA.A_NAME <> ''ETR'') AND (ALCOBACA.A_NAME <> ''TR'') AND
    (ALCOBACA.A_NAME <> ''DI'') AND (ALCOBACA.A_NAME <> ''DA'') AND
    (ALCOBACA.A_NAME <> ''PG'')') RT, hsttags Where RT.A_Tag = hsttags.Tag).

    Which is what i called T2.

    I want:
    UPDATE T1
    from T2
    Setting the T1_Col4 = T2_Col2
    Where T1_Col1= T2_Col1

    Hope to be explicit.

    Still working on this but without success.

    Regards,

  5. #5
    Join Date
    Jul 2007
    Posts
    5
    I solved it


    thanks.

  6. #6
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    "openquery" is not standard SQL. So you should consult your product manuals.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  7. #7
    Join Date
    Jul 2007
    Posts
    5
    The problem wasn't with openquery statment.
    It was really to do with my mistake on update statement syntax.
    Here's how i solved it.

    update t1
    set
    Descr = t2.A_desc
    from tags t1
    join (SELECT * FROM
    openquery(RealTFIX, 'SELECT DISTINCT (ALCOBACA.A_TAG) ,ALCOBACA.A_Desc FROM ALCOBACA ')RT, hsttags Where A_Tag = hsttags.Tag) t2 on
    t2.a_tag=t1.tag

  8. #8
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    Your UPDATE... FROM... JOIN is not standard SQL, it's a proprietary Microsoft feature. The "UPDATE FROM" syntax also has a nasty bug-feature: if the join criteria is non-unique in the table being joined to then it won't cause an error message, it will silently update your data with some random value from the rows being joined - very dangerous in my opinion.

    SQL Server 2008 supports MERGE, which IS standard SQL, is more powerful and generally more efficient than the equivalent UPDATE and doesn't suffer from the above problem. Always use MERGE and avoid UPDATE FROM wherever you can.

Posting Permissions

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