From: rpi@crl.com (Jack S. Moore)
Newsgroups: comp.databases.oracle
Subject: Re: Help with SQL syntax
Date: 2 Feb 1994 06:28:32 -0800
Organization: CRL Dialup Internet Access	(415) 705-6060  [login: guest]
Lines: 49
Message-ID: <2iodag$mjg@crl2.crl.com>
References: <CKL15H.IEE@bhpmhaw.bhp.com.au>
NNTP-Posting-Host: crl2.crl.com
Keywords: SQL


In article <CKL15H.IEE@bhpmhaw.bhp.com.au> ffakib@bhpmhaw.bhp.com.au (Banu Akin) writes:
 
>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
 

UPDATE second x
SET    item1 = (SELECT min(item) FROM first WHERE id = x.id);

UPDATE second x
SET    item2 = (SELECT min(item)
		FROM   first
		WHERE  id = x.id
		AND    item > x.item1)
WHERE  id in   (SELECT id FROM first GROUP BY id HAVING count(*) > 1);

UPDATE second x
SET    item2 = (SELECT min(item)
		FROM   first
		WHERE  id = x.id
		AND    item > x.item2)
WHERE  id in   (SELECT id FROM first GROUP BY id HAVING count(*) > 2);

L. Scott Johnson
Research Planning, Inc.
(803) 256-7322