Re: Help with SQL syntax

From: Jack S. Moore <rpi_at_crl.com>
Date: 2 Feb 1994 06:28:32 -0800
Message-ID: <2iodag$mjg_at_crl2.crl.com>

In article <CKL15H.IEE_at_bhpmhaw.bhp.com.au> ffakib_at_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 Received on Wed Feb 02 1994 - 15:28:32 CET