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

Claire Eskriett C.Eskriett at brighton.ac.uk
Thu Sep 20 12:21:00 BST 2012


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 at ecs.soton.ac.uk [mailto:eprints-tech-bounces at ecs.soton.ac.uk] On Behalf Of John Salter
Sent: 20 September 2012 12:04
To: 'eprints-tech at 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 at ecs.soton.ac.uk<mailto:eprints-tech-bounces at ecs.soton.ac.uk> [mailto:eprints-tech-bounces at ecs.soton.ac.uk]<mailto:[mailto:eprints-tech-bounces at ecs.soton.ac.uk]> On Behalf Of John Salter
Sent: 20 September 2012 11:55
To: 'eprints-tech at 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)
http://dev.mysql.com/doc/refman/5.1/en/alter-table.html

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 at ecs.soton.ac.uk<mailto:eprints-tech-bounces at ecs.soton.ac.uk> [mailto:eprints-tech-bounces at ecs.soton.ac.uk]<mailto:[mailto:eprints-tech-bounces at ecs.soton.ac.uk]> On Behalf Of Claire Eskriett
Sent: 20 September 2012 11:37
To: eprints-tech at ecs.soton.ac.uk<mailto:eprints-tech at 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 at brighton.ac.uk<mailto:C.Eskriett at 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/
___________________________________________________________
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://mailman.ecs.soton.ac.uk/pipermail/eprints-tech/attachments/20120920/f960a094/attachment-0001.html 


More information about the Eprints-tech mailing list