EPrints Technical Mailing List Archive

Message: #01105

< Previous (by date) | Next (by date) > | < Previous (in thread) | Next (in thread) > | Messages - Most Recent First | Threads - Most Recent First

[EP-tech] Re: MySQL query to update a column value

Ha ha! Thanks John

 

I use SQL to edit stuff on another database all the time (successfully), but this is different, and not my primary area (hence the checking). I shall check the MySQL for dummies again as I have clearly mis-read it….

 

Many thanks

 

Claire

 

From: eprints-tech-bounces@ecs.soton.ac.uk [mailto:eprints-tech-bounces@ecs.soton.ac.uk] On Behalf Of John Salter
Sent: 20 September 2012 12:04
To: 'eprints-tech@ecs.soton.ac.uk'
Subject: [EP-tech] Re: MySQL query to update a column value

 

 

>I think your initial command would have set the 'type' (eprint type) to 'text' for all your eprints. Eeek!

Actually, it should complain about an unknown column 'field'. If there was a column named 'field' in the eprints table, and it contained the value 'num_pieces' then the 'type' column for it would have been set to 'text'.

 

You did the right thing in checking - it's a dangerous (but powerful) thing having this level of database access :o)

 

From: eprints-tech-bounces@ecs.soton.ac.uk [mailto:eprints-tech-bounces@ecs.soton.ac.uk] On Behalf Of John Salter
Sent: 20 September 2012 11:55
To: 'eprints-tech@ecs.soton.ac.uk'
Subject: [EP-tech] Re: MySQL query to update a column value

NOOO! STOP STOP STOP!

 

An 'update eprint' command will edit the data in the table, not the structure of the table itself.

 

You need an 'ALTER TABLE' command, something like (this is untested, and off-the-top-of-my-head-and-therefore-possibly-not-right. Anyone else care to comment?):

ALTER TABLE eprint MODIFY num_pieces TEXT;

 

See this (or an appropriate version for your MySQL version)

 

I think your initial command would have set the 'type' (eprint type) to 'text' for all your eprints. Eeek!

 

Cheers,

John

 

From: eprints-tech-bounces@ecs.soton.ac.uk [mailto:eprints-tech-bounces@ecs.soton.ac.uk] On Behalf Of Claire Eskriett
Sent: 20 September 2012 11:37
To: eprints-tech@ecs.soton.ac.uk
Subject: [EP-tech] MySQL query to update a column value

Hi all

 

I’ve edited eprints.pl to change the property of an eprint field from an integer to text but running update_database_structure has not updated the eprint table. I’ve been advised to update manually using MySQL and have come up with the following:

 

Use <database name>

Update eprint

Set type = text

Where field = num_pieces

 

I think this is ok – I’ve specified the table (eprint) and the field that I want to update (num_pieces) and the column that I want updating is ‘type’. The current row in the table is as follows:

Field                             | Type         | Null | Key | Default | Extra

num_pieces            | int(11)      | YES  | MUL | NULL    |       |

 

Can anyone take pity on me and reassure me that this is ok? I ask as a MySQL novice.

 

Many thanks for any advice!

 

Claire

 

 

Claire Eskriett, Systems Librarian

(Days of work are Mon, Tues, Thurs & Fri)

Information Services

Cockcroft Building

University of Brighton

Lewes Road

Brighton BN2 4GJ

T: 01273 642766

E: C.Eskriett@brighton.ac.uk

 


___________________________________________________________
This email has been scanned by MessageLabs' Email Security
System on behalf of the University of Brighton.
For more information see http://www.brighton.ac.uk/is/spam/
___________________________________________________________

___________________________________________________________
This email has been scanned by MessageLabs' Email Security
System on behalf of the University of Brighton.
For more information see http://www.brighton.ac.uk/is/spam/
___________________________________________________________


___________________________________________________________
This email has been scanned by MessageLabs' Email Security
System on behalf of the University of Brighton.
For more information see http://www.brighton.ac.uk/is/spam/
___________________________________________________________