Обсуждение: Update entire column with new date values

Hi all,

I have a simple table which contains information about our safety files:

CREATE TABLE docs
(
  filename varchar(256) NOT NULL,
  filepath varchar(256) NOT NULL,
  version varchar(8),
  date timestamp NOT NULL,
  docid int8 NOT NULL DEFAULT nextval('docs_docid_seq'::regclass),
  category varchar(16), NOT NULL
  CONSTRAINT pkey_docs PRIMARY KEY (docid, filename, date),
  CONSTRAINT ukey_docid UNIQUE (docid)
)

I need to update the date for each file (the "date" & "filename"
columns). However, the updated values for date are not the same for each
file (due to when the files were last modified). I can import the new
date values from a text file containing the date and filename.

What sort of "UPDATE" command do I use in this case? Essentially, I'm
trying to do something like:

UPDATE docs SET date = '{$input_date}'
    FROM ('C:/Temp/docupdate.txt')
    WHERE filename = '{$input_filename}';

Any help is appreciated. Thanks!


Kevin Weiss
kevin.weiss@selkirkinc.com

Weiss, Kevin wrote:
> I need to update the date for each file (the "date" & "filename"
> columns). However, the updated values for date are not the same for each
> file (due to when the files were last modified). I can import the new
> date values from a text file containing the date and filename.
>
> What sort of "UPDATE" command do I use in this case? Essentially, I'm
> trying to do something like:
>
> UPDATE docs SET date = '{$input_date}'
>     FROM ('C:/Temp/docupdate.txt')
>     WHERE filename = '{$input_filename}';

You'll want to gather your updates into a table and then use the
non-standard FROM clause to do pretty much what you're trying to here.

http://www.postgresql.org/docs/8.1/static/sql-update.html

UPDATE docs SET date = ???
FROM newvals
WHERE docs.filename=newvals.filename

Failing that look at a subselect in your SET clause.

--
   Richard Huxton
   Archonet Ltd

am  27.07.2006, um 10:17:48 -0500 mailte Weiss, Kevin folgendes:
> I need to update the date for each file (the "date" & "filename"
> columns). However, the updated values for date are not the same for each
> file (due to when the files were last modified). I can import the new
> date values from a text file containing the date and filename.
>
> What sort of "UPDATE" command do I use in this case? Essentially, I'm
> trying to do something like:
>
> UPDATE docs SET date = '{$input_date}'
>     FROM ('C:/Temp/docupdate.txt')
>     WHERE filename = '{$input_filename}';
>
> Any help is appreciated. Thanks!

Create a new table with filename and date - column and fill this table
with your text file. Then you have 2 tables and can run normal SQL to
update your table.


HTH, Andreas
--
Andreas Kretschmer    (Kontakt: siehe Header)
Heynitz:  035242/47215,      D1: 0160/7141639
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
 ===    Schollglas Unternehmensgruppe    ===