MySql

[dan gulberry]

Anyone here know anything about MySql? I have to update a database and have no idea how to do it!

I was sent this link by the guy who has left me in the shit: http://dev.mysql.com/doc/refman/5.0/en/update.html

It's ok but I'm still overwhelmed.
Anyone able to take me through it? Pleeeaase???

dg

[AVX23]

Anyone here know anything about MySql? I have to update a database and have no idea how to do it!

I was sent this link by the guy who has left me in the shit: http://dev.mysql.com/doc/refman/5.0/en/update.html

It's ok but I'm still overwhelmed.
Anyone able to take me through it? Pleeeaase???

dg

how much will you pay ? :)

LOL only kidding, what is it you are trying to do , the doc's u've added links for are just syntax, what is the actual task in hand ?

[Ava]

Can you give us some more information?


Are you sure this guy doesn't mean for you to update a database through some sort of user-facing front-end to it (ie update a database of contacts, or products, or whatever). Normally MySQL acts as backend storage for some kind of simpler user interface, and plain SQL queries (such as UPDATE) are only used during development and are not seen by a day to day user.

Or maybe you are asking for help with writing SQL queries, but it's hard to tell from your post!

more details and background please!

[dan gulberry]

Thanks both. And no money, I'm afraid! The task at hand is to UPDATE the entire database. I've looked at the manual but do not have the knowledge to 'fill in the blanks'.

There is a front-end through which I can update it, and yes I have to construct a 'query' of some kind. So, help with writing queries is what I need. I think.

Here is what I had to enter to delete some items from the database, just to give you an idea:

DELETE FROM features WHERE image_front LIKE "%Germany/0058a%"

[AVX23]

man, sounds like you may be a little unprepared for the task, for a start, what is it you be updating, and where, without knowing the architecture of the database - table names etc, It's hard to advise nevermind not knowing what the required queries are intended to do..

I'm guessing you wanna do lots of different updates - delete, insert etc on multiple tables and stuff - hence not using the frontend.

If u need , can certainly take a look for you, dunno how much help it will be, is it a live dataset ?

[Spandex]

Do a websearch for "SQL Tutorial" and you'll find plenty. Can help with any specific questions.

Top Tips
1) Backup the entire DB before you start

2) Use PHPMyAdmin if you can

3) Whenever you do an UPDATE or DELETE statement, run it as a SELECT first and check it's only getting the rows you want.. e.g. if you want to "UPDATE user SET status='live' WHERE username LIKE 'dan%'" then before you run that just do a "SELECT * FROM user WHERE username LIKE 'dan%'" to see which rows it will affect when you update.

[kams]

tell your boss that you can't do it unless they pay for you to train

[mdk]

4) Listen to spandex.

[dan gulberry]

I'm am totally out of my depth! Thanks again for the advice. I guess some more background is in order.

I co-created an interface that would be used to code a dataset (about 1000 digitised postcards). There were lots of analytic categories, which I assume are considered 'columns' in terms of the database. Once coded, we discovered some other problems that I had to go and fix. Once fixed, we were supposed to update the entire database, i.e. all columns for all cards. I also assume that these columns are organised by TABLES.

I've hopefully hotlinked to a jpg of the coding interface so you can see how the categories are organised on the front end. It's not the entire image but you get the idea:

http://i89.photobucket.com/albums/k227/dangulberry/th_coding1.jpg (http://i89.photobucket.com/albums/k227/dangulberry/coding1.jpg)

[dan gulberry]

,,,

[dan gulberry]

4) Listen to spandex.

I know. :)

[love_tempo]

I think the best thing to do is to spend a couple of hours reading a good SQL tutorial before you even begin. Try this one (SQL for Web Nerds, Chapters 1-5 and especially chapter 5):

http://philip.greenspun.com/sql/

Also, I suggest you backup the database after every major successful operation. That way you can at least easily roll back to the last good DB if you make a mistake, which you probably will :) Or use transactions if the tables are stored as InnoDB as opposed to MyISAM. InnoDB is usually the default for MySQL.

That's the most important thing, but also beware of queries that start with DELETE FROM and don't end in a WHERE clause! That's the fastest way to delete an entire table.

One way I see non-technical people using MySQL here through the MySQL Query Browser client. Using that, you can use a GUI to create tables/drop tables and delete/update/add rows without using SQL. You might find that easier to begin with.

[dan gulberry]

good advice lovetempo. I'm going to read that tutorial and have a go on the Query Browser. Can I backup the database with that?

[love_tempo]

No problem, the query browser can be a little bit buggy but should help you a lot. When you are adding or editing tables it shows you the sample syntax too which can be handy when learning your way around. One thing to be aware of is that you can usually only edit a tables records with the GUI if there is a Primary Key on the table.

As for backing up, one of the nicest things about MySQL is that you can just copy the folder containing the DB or use your favourite compression tool. The folder will have the same name as the database and will probably be in a sub-directory of the MySQL installation or somewhere equally obvious. Beware if you are restoring a previously backed up database as sometimes you need to restart the MySQL service using the MySQL administrator program.

Alternatively, there's a nice command line tool called mysqldump that can be used to backup and restore DBs in a more politically correct way...probably OTT for what you want.

[philie-t]

this is a peice of piss? what sthe problem? you have asked the most Basic fucking SQL question Ever. Even my mum could probably do this and thats not an exagerattion

[dan gulberry]

Cheers Phil. Can you ask your mum to contact me on...Oh wait, I already have her number.

I can't be fucked with this. When I said that somebody left me the shit I didn't realise how true that actually was! The database guy's contract has ended and he's unwilling to even field questions about the basic things I would need to know in order to perform an update. So, even if I spent the whole week reading up on sql, I still would not know how tables are named, etc. It's like knowing a language's grammar but no lexical items.

Thanks for everyone's help, anyway.

[dan gulberry]

Fuuuuuuuuuck!!!!!

[penciLneck]

really feel for you, but not exactly sure what you are trying to do. Spandexs top tip about the select is great advice, I always do that. there is loads of good advice here that doesn't need repeating. is everyone a linux geek on here? mad.

[dan gulberry]

I've now managed to squeeze some more info out of the twat, such as table names etc. Seems a lot easier now. Will report. Thanks everybody.

[thesnailsshow]

you can update the tables as normal "Excel" Sheets with a program Called MySQL Query Browser. It's free and you can DL it from mysql.com