SQL Server Performance

Help (Correction)

Discussion in 'General Developer Questions' started by pharoah35, Sep 9, 2003.

  1. pharoah35 New Member

    Good morning good people.

    I am having a bit of a problem with the following T-SQL Syntax.
    I was hoping that one of you kind Ladies or Gentlemen could lend some assistance.

    I am referencing 2 tables listed below via the Select statement
    dbo.tblproitem & dbo.tblingrampb,

    I need to update the [Brand] column in table dbo.tblingrampb with 'COMPAQ' where the value in column [vendid] in table dbo.tblproitem is the same as the value in [vend id] in table dbo.tblingrampb and the [progid] in table dbo.tblproitem = 21099

    Here is the syntax I used.

    ----------------------------------------------------------------------
    SELECT * from dbo.tblproitem, dbo.tblingrampb

    update dbo.tblingrampb
    set dbo.tblingrampb.[Brand] = 'COMPAQ'

    Where dbo.tblproitem.[vendid] = dbo.tblingrampb.[vend id] and dbo.tblproitem.[ProgID] = '21099'
    ----------------------------------------------------------------------
    I get the following error message via QA (Query Analyzer)

    ----------------------------------------------------------------------
    Server: Msg 107, Level 16, State 3, Line 1
    The column prefix 'dbo.tblproitem' does not match with a table name or alias name used in the query.
    Server: Msg 107, Level 16, State 1, Line 1
    The column prefix 'dbo.tblproitem' does not match with a table name or alias name used in the query.
    ----------------------------------------------------------------------

    I would appreciate any assistance you may be able to provide
    Thanks in advance
    Pharoah35
  2. Twan New Member

    Hi there,

    you can use



    update i
    set [Brand] = 'COMPAQ'
    from dbo.tblingrampb i
    inner join dbo.tblproitem p
    on p.[vendid] = i.[vend id]
    where i.[ProgID] = '21099'

    Cheers
    Twan

    PS I've used i and p here for ease of reading/typing. You can use the full table name instead if you prefer.
  3. pharoah35 New Member

    Dear Twan

    Thank you so very much for your assistance.
    That really did the trick.

    Pharoah35

Share This Page