Help with SQL syntax

Hi there,

I need some help with SQL syntax. I have a table with the following data in it. I want to update another table using this data with different format. Let's call the first table FIRST and the second table SECOND.

   FIRST		SECOND (before update)
   =====		======
ID	ITEM		ID	ITEM1	ITEM2	ITEM3
---	----		--	-----	-----	-----
1	a		1
1	b		2
1	c		3
2	c		4
2	b		
3	d		
4	a
4	d

   SECOND (after update)

ID	ITEM1	ITEM2	ITEM3
--	-----	-----	-----
1	a	b	c
2	c	b
3	d
4	a	d

I need an SQL statement that will select the rows in FIRST and update ITEM1, ITEM2, ITEM3 columns in SECOND table. I do not need to update all 3 columns in the same SQL statement.

I tried the following SQL statement,

update SECOND X
set ITEM1 = (select ITEM from FIRST, SECOND

		where FIRST.ID = SECOND.ID
		and SECOND.ID = X.ID
		and rownum = 1);

it worked OK for ITEM1, but it didn't work for ITEM2 and ITEM3.

Any help is very much appreciated.

Banu Akin (akin_at_bhpmhaw.bhp.com.au) Received on Wed Feb 02 1994 - 06:41:40 CET