SQL Update 4 Lookups


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 2 of 2

Thread: SQL Update 4 Lookups

Hybrid View

  1. #1
    Werner Guest

    SQL Update 4 Lookups


    Maybe this is a stupid beginner-question...

    Someting I could do in Access (2, 97), but not in SQL Server 2000(example):

    - In a table tblPCs I originally had a field with PCTypes (as text). I want
    to replace it by a foreign key PCTypeID.

    - I add the column PCTypeID to tblPCs, build a new table tblPCType with
    PC PCTypeID.

    - The problem is to insert the right PCTypeIDs into tblPCs. In access I
    did it with this query, joining the two tables:

    UPDATE DISTINCTROW tblPCs
    LEFT JOIN tblPCTypes ON tblPCs.PCType = tblPCTypes.PCType
    SET tblPCs.PCTypeID = tblPCTypes.PCTypeID

    - In SQL Server this does not work (don't quite remember the errormessage
    right now), he seems to like only "one record updates"!?

    - What surprises me: when I did this once in Access and watched, what SQLServer
    was doing (with a large table), it looked like 3 statements per row, not
    like one SQL statement.

    What is the right way to do it in SQL Server. If the answer is: do it on
    a record by record base, I know how to do it (e.g. stored procedure with
    cursor, walking through records or ADO recordset in VB-Program).

    Anything better than that???

    Thanks in advance.

    Kindly yours

    Werner Zoller
    Share on Google+

  2. #2
    Rob Vieira Guest

    Re: SQL Update 4 Lookups

    <MiscRant>
    Just for the record, I positively hate the "tbl" prefix on tables - never
    did make any sense to me, though some people continue to do it (to each his
    own I guess).
    </MiscRant>

    OK, so, moving on...

    Assuming that you want to update the new column tblPCs.PCTypeID with values
    from tblPCType.PCTypeID where the column you can join on is called PCTypes,
    it would work something like this (Untested, as I don't have the same schema
    set up).

    UPDATE pc
    SET pc.PCTypeID = pct.PCTypeID
    FROM tblPCTypes pct
    JOIN tblPCs pc
    ON pct.PCType = pc.PCType


    The above syntax assumes no WHERE clause (which would just be tacked onto
    the end). I also steered away from the LEFT JOIN you had as it made no sense
    to me (I'm presuming - perhaps a dangerous thing), that you have already
    established a NULL value or some other default when you created the new
    column. That will remain for columns where there is no match.

    HTH,

    --
    Rob Vieira MCSD, MCT, MCDBA
    www.ProfessionalSQL.com


    "Werner" <wzoller@remove.nospam.mbbm.de> wrote in message
    news:3a704e9b@news.devx.com...
    >
    > Maybe this is a stupid beginner-question...
    >
    > Someting I could do in Access (2, 97), but not in SQL Server

    2000(example):
    >
    > - In a table tblPCs I originally had a field with PCTypes (as text). I

    want
    > to replace it by a foreign key PCTypeID.
    >
    > - I add the column PCTypeID to tblPCs, build a new table tblPCType with
    > PC PCTypeID.
    >
    > - The problem is to insert the right PCTypeIDs into tblPCs. In access I
    > did it with this query, joining the two tables:
    >
    > UPDATE DISTINCTROW tblPCs
    > LEFT JOIN tblPCTypes ON tblPCs.PCType = tblPCTypes.PCType
    > SET tblPCs.PCTypeID = tblPCTypes.PCTypeID
    >
    > - In SQL Server this does not work (don't quite remember the errormessage
    > right now), he seems to like only "one record updates"!?
    >
    > - What surprises me: when I did this once in Access and watched, what

    SQLServer
    > was doing (with a large table), it looked like 3 statements per row, not
    > like one SQL statement.
    >
    > What is the right way to do it in SQL Server. If the answer is: do it on
    > a record by record base, I know how to do it (e.g. stored procedure with
    > cursor, walking through records or ADO recordset in VB-Program).
    >
    > Anything better than that???
    >
    > Thanks in advance.
    >
    > Kindly yours
    >
    > Werner Zoller



    Share on Google+

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center