From: Gary Stainburn <gary.stainburn@ringways.co.uk>
To: pgsql-sql@postgresql.org
Subject: table update using result from join
Date: Mon, 11 Oct 2004 10:49:24 +0100
Message-ID: <200410111049.24363.gary.stainburn@ringways.co.uk> (raw)
Hi folks.
I'm annoyed that I can't sus out something this simple.
The join below gives the output I want, but I can't work out how to
convert it to be an update on the requests field.
I want to append the customer name to the fuel (delivery details) field
and then update the customer field to be 'retail'. but I can't work out
the description update bit.
goole=# select r.r_fuel||' - Customer: '||c.c_des from requests r,
customers c
goole-# where r.r_c_id = c.c_id and c.c_id in (
goole(# 9, 10, 13, 27, 35, 36, 39, 42, 44, 51, 53, 54, 55, 56, 57, 58,
60, 65, 67, 69,
goole(# 74, 75, 77, 81, 82, 84, 88, 89, 90, 91, 92, 96, 98, 99, 100,
101, 102, 103,
goole(# 105, 108, 113, 114, 117, 118, 124, 125, 126, 131, 132, 135, 136,
137, 144,
goole(# 145, 148, 149, 150, 151, 154, 11, 37, 40, 41, 43, 48, 52, 59,
62,
goole(# 63, 68, 70, 71, 83, 86, 93, 104, 119, 120, 121, 123, 128, 129,
130,
goole(# 134, 138, 142, 146, 147, 152, 153, 19, 38, 85, 87, 94, 106, 112,
116,
goole(# 141, 143, 18, 110, 111, 115, 140, 24, 50, 133, 47, 64, 76, 95,
107,
goole(# 109, 127, 33, 46, 97);
?column?
-------------------------------------------------------------------------------------
RINGWAYS - Customer: SUBSCAN
RINGWAYS - Customer: MARTIN
? - Customer: N G BAILEY
? - Customer: CHECRON SITE SERVICES LTD
10 litres - Customer: CONSULTANT SERVICES
? - Customer: ALD AUTOMOTIVE
eskrigg yo19 6ez 9am - Customer: TRANSPORT MANAGEMENT
TRAILER / PE18 9UH / HALF ON DELIVERY - Customer: ALD AUTOMOTIVE
DRIVEN / WF16 0NF - Customer: CONSULTANT SERVICES
TRAILER / HALF TANK ON DELIVERY - Customer: LEX / SWINTON
DN14 0HR - Customer: CHECRON SITE SERVICES LTD
. - Customer: other fleet
[snip]
(256 rows)
goole=#
What I want is something like
update requests set r_fuel=<what>, r_c_id = 7 where r_c_id in (.......
--
Gary Stainburn
This email does not contain private or confidential material as it
may be snooped on by interested government parties for unknown
and undisclosed purposes - Regulation of Investigatory Powers Act, 2000
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: gary.stainburn@ringways.co.uk
Subject: Re: table update using result from join
In-Reply-To: <200410111049.24363.gary.stainburn@ringways.co.uk>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox