help writing update query

Hi,

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

thanx

Wim

LVL 6
wimthepimscakeAsked:
Who is Participating?
 
bobsteryCommented:
Update yourtable
set section_parentID = (select section_id from yourtable where t.section_parentID = section_deflangID)
from yourtable t
0
 
mokuleCommented:
UPDATE yourtable a SET section_parentID=(SELECT TOP 1 section_id FROM yourtable b WHERE a.section_parentID=b.section_deflangID)
0
 
wimthepimscakeAuthor Commented:
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
0
 
MannaraprayilCommented:
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.