SQL for Smarties | SQL Programming Style | Trees and Hierarchies in SQL | SQL Puzzles and Answers | Data and Databases


Wednesday, August 30, 2006

inner join using SubString

SQL Apprentice Question
On the below, I'm trying to inner join on 2 fields.

example data:
stcdl.od = 'AZDB1234'
itk.od = '1234'


UPDATE itk
SET itk.ta = stcdl.ta
FROM itk
INNER JOIN stcdl ON SubString(stcdl.od,5,4) = itk.od


Obviously, what I'm trying to do does not work.
What will work?


Celko Answers
Let's try the Standard SQL syntax instead of the unpredictable
proprietary T-SQL syntax:

UPDATE itk
SET ta
= (SELECT stcdl.ta
FROM stcdl
WHERE SUBSTRING (stcdl.od, 5, 4) = itk.od);


If the scalar subquery returns nothing, you will get a NULL assigned.
If the scalar subquery returns more than one row , you will get a
cardinality violation.

No comments: