very basic sql, im stuck

Forums › IT Pro and developers › very basic sql, im stuck

s.joseph

590 posts

Ultimate Geek

Trusted

#58556 14-Mar-2010 16:52

how would i go about chaging the value of something ive added in a database in sql

eg. originally i inserted this.

Insert into Fruit values
(1, 'Apple', '$4.99'),
(2, 'Orange','$3.99'),

what script would i use to change oranges to 2.99?
is it update?

its for my uni submission but i cant experiment on sql because my computer has flipped out.

jeffreyh

54 posts

Master Geek

#307114 14-Mar-2010 16:59

update fruit set <whatever the $ value column is called>='$2.99'
where <whatever you have called the id column>=2

s.joseph

590 posts

Ultimate Geek

Trusted

#307115 14-Mar-2010 17:03

thank you!

rvangelder

352 posts

Ultimate Geek

#307162 14-Mar-2010 19:14

you're storing the amounts as a string? really?
you should store numeric data as a numeric data type. do the formatting later, in the application or report.

assuming you've changed the amount datatype to a number:
update fruit set amount = 2.99 where fruit_id = 2;

otherwise as string:
update fruit set amount = '$2.99' where fruit_id = 2;

nate

6473 posts

Uber Geek

Retired Mod

Trusted

Lifetime subscriber

#307226 14-Mar-2010 22:39

rvangelder: you're storing the amounts as a string? really?
you should store numeric data as a numeric data type. do the formatting later, in the application or report.

+1

Definitely start good habits now, plenty of reasons why to store data correctly - uses less space, better performance, easier to write queries etc.

ReaperSA

3 posts

Wannabe Geek

#307528 15-Mar-2010 20:31

PS The insert will fail...

You need to specify destination columns in your insert query i.e.
Insert into Fruit(IDField,FruitName,Price) values
(1, 'Apple', '$4.99'),
(2, 'Orange','$3.99'),

Also rather use an autonumber field for the first column as this will allow you to index your datatables making queries faster.

Just my humble opinion. :)

rvangelder

352 posts

Ultimate Geek

#307577 15-Mar-2010 22:11

I think it's good practise to name the columns too, but his SQL won't fail for that reason.
Named columns are optional (depending on the database).
http://download.oracle.com/docs/cd/B14156_01/doc/B13812/html/sqcmd.htm#BABGFGDA

A few tidy ups for those inserts
1 insert statement per row of data:
insert into fruit (fruit_id, name, amount) values (1, 'Apple', 4.99);
insert into fruit (fruit_id, name, amount) values (2, 'Orange', 3.99);

If you're developing an application, bind parameters wherever you can.
Not only will it increase performance by saving the database from re-parsing your SQL and calculating a query path for every line, it can protect your application from a security problem called "sql injection". http://xkcd.com/327/. You'll immediately notice vulnerable websites - those that can't handle an apostrophe in a text field.