• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 197
  • Last Modified:

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

0
wimthepimscake
Asked:
wimthepimscake
3 Solutions
 
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
 
bobsteryCommented:
Update yourtable
set section_parentID = (select section_id from yourtable where t.section_parentID = section_deflangID)
from yourtable t
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

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now