[RESOLVED] SQL Question

[belebala]

Anyone could help me to create the following sql update statement?

Here is my table:

Primary Key | Foreign Key | Description
1 | 1 | a
2 | 1 | null
3 | 2 | b
4 | 2 | null
5 | 3 | c
6 | 3 | null

I would like to copy the data on Description column if the Foreign Key is the same. Here is the outcome I would like to have:
Primary Key | Foreign Key | Description
1 | 1 | a
2 | 1 | a
3 | 2 | b
4 | 2 | b
5 | 3 | c
6 | 3 | c

Thanks.

[kevin.horgan]

Hi belebala,

Try joining the table on its self. Depending on which database you are using it would be something like this.

UPDATE yourTable t1 SET t1.description = t2.description
FROM yourTable t2
WHERE t1.foreignkey = t2.foreignkey
AND COALESCE(t2.description,'') = ''

If your DB does not support COALESCE look for something like "IS NULL" in the database documentation. Basically you want to join "yourTable" on it's self. Where the foreignkey is the same and the description field is a NULL value. Don't try to do something like "WHERE ...AND t2.description = NULL" This is not valid in many databases as NULL = NULL is invalid.

Cheers,

Kevin

[belebala]

Thank you for your reply Kevin,

I'm using Oracle SQL Developer and when I executed the above sql statement, I got the following error:
Error at Command Line:1 Column:69
Error report:
SQL Error: ORA-00933: SQL command not properly ended
00933. 00000 - "SQL command not properly ended"
*Cause:
*Action:

Not sure is it this software doesn't support "UPDATE .. FROM"...

Thanks.

[kevin.horgan]

Hi belebala,

I am guessing it is the FROM clause in the UPDATE which Oracle does not like. I can not remember if Oracle supports that or not. Here is an alternative to try which might also work if the FROM clause is not supported in PL SQL.

UPDATE yourTable t1 SET t1.description = (SELECT t2.description FROM yourTable t2 WHERE t1.foreignkey = t2.foreignkey AND t2.description IS NULL);

Maybe that will work for you.

Cheers,

Kevin

[belebala]

After I executed this statement, I got this error "single-row subquery returns more than one row". And I also tried to replace the equal sign by "IN" but then I received an error of "missing equal sign". Any idea how to fix this? Thank you.

[davide++]

Hi all.

I doubt you'll able to solve your problem using a SQL command only.
I suggest to write PL/SQL code; if you're going to run the script once to adjust your table, you can write an anonymous PL/SQL script.

[kevin.horgan]

Hi,

OK here is a corrected version of the UPDATE statement.

UPDATE myTable t1 SET t1.description = (SELECT t2.description FROM myTable t2 WHERE t1.foreignkey = t2.foreignkey AND t2.description IS NOT NULL)
WHERE t1.description IS NULL;

This should work for the test data you provided in the initial question.

Good luck,

Kevin

[belebala]

Hello Kevin,

Your UPDATE statement works great! Thank you very much for your help.

belebala

[davide++]

Hi,

OK here is a corrected version of the UPDATE statement.

UPDATE myTable t1 SET t1.description = (SELECT t2.description FROM myTable t2 WHERE t1.foreignkey = t2.foreignkey AND t2.description IS NOT NULL)
WHERE t1.description IS NULL;

This should work for the test data you provided in the initial question.

Good luck,

Kevin

Good.
I surrendered too soon.

(To tell the true, I tried but I missed the last part, the WHERE condition on t1)