76627

SQL: Populating Column B where Column A has a match elsewhere in Column B

Question:

I’m somewhat of a newbie to SQL queries, especially anything containing logic, and although I've searched for hours finding the exact terms to search for is not easy in this case! I have a relatively simple one, I’m sure:

A table has 2 columns, and each row contains data about a function in a program. Some functions have a parent function associated (for grouping). Column A is the unique function ID. Column B indicates, when applicable, the parent function’s ID. All parent function IDs are independent and valid function IDs that exist elsewhere in column A.

For reporting purposes I need to list the functions grouped by their parent ID, listing the parent function with the child functions. I can easily report by parent function ID, but the problem is that a parent function does not know that it is a parent function because its column B is empty!

What I need to do is complete the value in Column B if it is empty and the function is referenced elsewhere as a parent function.

Otherwise stated, <strong>for each row</strong> that has a null value in Column B:

<ol><li>Take the value from column A</li> <li>Check for the existence of that value in ANY row on column B</li> <li>If there is a match, inject the value into column B (so that Column A and B have the same value)</li> </ol>

What I have: (Query: SELECT function_id, parent_function FROM functions)

FUNCTION_ID PARENT_FUNCTION 4 13 4 79 138 4 195 314 345 345

What I need to have:

FUNCTION_ID PARENT_FUNCTION 4 4 13 4 79 138 4 195 314 345 345 345

Any Ideas? I can't wait to get more familiar with SQL! Thanks ahead of time.

Answer1:

This should work for you:

UPDATE functions SET parent_function = function_id WHERE parent_function IS NULL AND function_id IN (SELECT parent_function FROM functions)

This will set parent_function equal to function_id where it has not yet been set, and where it appears somewhere in the parent_function column.

If you don't actually want to modify the table data but still return values that you need, you can use similar logic like this:

SELECT f.function_id, COALESCE(f.parent_function, f2.function_id) as parent_function FROM functions f LEFT JOIN functions f2 ON f.function_id = f2.function_id AND f2.function_id IN (SELECT parent_function FROM functions)

Answer2:

maybe you can compare the two table using EXCEPT or INTERSECT

http://msdn.microsoft.com/en-us/library/ms188055.aspx

more tutorials>:

http://www.mssqltips.com/sqlservertip/1327/compare-sql-server-datasets-with-intersect-and-except/

Answer3:

How's this look?

select distinct t1.funx, t1.parent, case when t2.parent is null then t1.parent else t2.parent end as newparent from tbl t1 left outer join tbl t2 on t1.funx = t2.parent

<a href="http://sqlfiddle.com/#!3/55d23/15/1" rel="nofollow">sqlFiddle</a>

Recommend

  • Adding libraries to portable python
  • Way to represent unknown file size in FTP LIST?
  • CONVERT MySQL Query to SQL Server (MSSQL / SQLSRV) (WiTH DISTINCT)
  • Celery + Redis - .get() hangs indefinitely after running smoothly for ~70 hours
  • Is it good practice to put Edge Side Includes into my templates?
  • What is happening when this code calls FUSE like this?
  • How do I calculate a grouped z score in R using dplyr?
  • Moving data between processes in Spartan 3
  • Taking mean across rows grouped by a variable in numpy
  • Adding new column to DataFrame with values dependent on index ref
  • Safari PHP form submission -file upload hangs
  • Disable/remove close icon on Kendo Grid's default group column
  • Copying rows in a database when rows have children
  • Jquery UI Sortable, move item automatically
  • Passing information to server-side function in a Google Docs Add On
  • Generic classes with Collection getter of other types
  • AWS-SES: Handling Bounces for Invalid ISPs
  • Why doesnt this Java loop in a thread work?
  • apply a javascript function to draggable copy
  • Aptana 3 remove bundle (jquery)
  • netsh acl setting (need alternative method - registry settings?)
  • Updating both a ConcurrentHashMap and an AtomicInteger safely
  • Security issues with PHP's Readfile method
  • Unable to decode certificate at client new X509Certificate2()
  • Xcode 4 NSLog Macro link in Xcode 3
  • Xamarin Forms - UWP Fonts
  • Apache 2.4 and php-fpm does not trigger apache http basic auth for php pages
  • How to extract text from Word files using C#?
  • Join two tables and save into third-sql
  • How to model a transition system with SPIN
  • Release, debug version and Authorization Google?
  • ORA-29908: missing primary invocation for ancillary operator
  • Is there a mandatory requirement to switch app.yaml?
  • File not found error Google Drive API
  • Turn off referential integrity in Derby? is it possible?
  • Qt: Run a script BEFORE make
  • Add sale price programmatically to product variations
  • Unable to use reactive element in my shiny app
  • Converting MP3 duration time
  • How do I use LINQ to get all the Items that have a particular SubItem?