help writing update query

Posted on 2005-04-25
Last Modified: 2010-03-19

I have a table like this:

section_id  section_deflangID  section_parentID
-----------  --------------------  ----------------
249           111                      121 (this value should be replaced by *)
250           116                      121
251*         121                      NULL
252+         124                      121
253           125                      124 (this should be replaced by +)
254           127                      121
260           133                      127
261           134                      121

what I need is a query that updates the section_parentID with the section_id that has the corresponding section_deflangID
I have put some remarks in the tabe above as an example



Question by:wimthepimscake
    LVL 17

    Assisted Solution

    UPDATE yourtable a SET section_parentID=(SELECT TOP 1 section_id FROM yourtable b WHERE a.section_parentID=b.section_deflangID)
    LVL 6

    Author Comment

    I can not seem to use an alias in an update statement, it keeps giving me an error on the yourtable a, if I do it like this:

    UPDATE yourtable SET section_parentID=(SELECT TOP 1 b.section_id FROM yourtable b WHERE section_parentID=b.section_deflangID)

    All section_parentID's end up beeing NULL

    Accepted Solution

    Update yourtable
    set section_parentID = (select section_id from yourtable where t.section_parentID = section_deflangID)
    from yourtable t
    LVL 1

    Assisted Solution

    Update yourtable
    set section_parentID = ISNULL((select section_id from yourtable where t.section_parentID = section_deflangID), section_parentID)
    from yourtable t

    This will avoid inserting NULL into the table

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    This is my first ever Article on EE or elsewhere; therefore, please bear with me if I have some discrepancies in my writing. I read many articles and questions related to "how to pass values to SSIS packages at run-time?"  Hence, this common ques…
    This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
    This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
    Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now