insert into where column = somevalue

Dev Shed Forums Sponsor:

August 30th, 2005, 07:42 AM

Javaholic

Contributing User

Join Date: Jul 2005

Posts: 34

Time spent in forums: 1 Day 3 h 20 m 40 sec
Reputation Power: 4

insert into where column = somevalue

Code:

insert into SJ5752.quadrat_dataplots(HEIGHT,SPECIES,STATE,MORPH)
select HEIGHT,SPECIES,STATE,MORPH from SJ5752.quadrat_dataplots where PLOTID ='plot 1'

Code:

insert into SJ5752.quadrat_dataplots(HEIGHT,SPECIES,STATE,MORPH)
    values('12.5','sg','l','no') where PLOTID = 'plot 1'

I have tried both of the above and both failed to work.

I want to insert only into a row where PLOTID = some value

What can i do?

August 30th, 2005, 07:53 AM

pabloj

Modding: Oracle MsSQL Firebird

Dev Shed God 6th Plane (7500 - 7999 posts)

Join Date: Jun 2001

Location: Outside US

Posts: 7,917

Time spent in forums: 2 Months 3 Weeks 3 Days 14 h 46 m 3 sec
Reputation Power: 279

Maybe you are trying to update an existing row where PLOTID = 'plot 1' with new values?

__________________
My blog
Tutorials about OSS databases, DBMonster ...
Contribute to OSS Development, fill bug reports!
Developer Shed eSupport
Commented my.ini/my.cnf (ADD YOUR OWN CONFIG TRICK)
An introduction to database normalization
Natural or Surrogate key
Custom ordering for your results
Correlated and uncorrelated subqueries
Don't turn your outer joins into inner joins
Random data (with a bias)

August 30th, 2005, 07:55 AM

shammat

Contributing User

Join Date: Oct 2003

Location: Munich, Bavaria

Posts: 1,007

Time spent in forums: 1 Week 3 Days 13 h 19 m 21 sec
Reputation Power: 67

You have to use UPDATE.
INSERT creates a new row, UPDATE is used to modify existing ones

August 30th, 2005, 08:06 AM

Javaholic

Contributing User

Join Date: Jul 2005

Posts: 34

Time spent in forums: 1 Day 3 h 20 m 40 sec
Reputation Power: 4

Quote:

Originally Posted by shammat

You have to use UPDATE.
INSERT creates a new row, UPDATE is used to modify existing ones

Thank you

August 30th, 2005, 09:07 AM

Javaholic

Contributing User

Join Date: Jul 2005

Posts: 34

Time spent in forums: 1 Day 3 h 20 m 40 sec
Reputation Power: 4

I tried:

Code:


update SJ5752.quadrat_dataplots
set (HEIGHT,SPECIES, STATE,MORPH) = (
select HEIGHT,SPECIES,STATE,MORPH from SJ5752.quadrat_dataplots where PLOTID = 'plot 1'
)

but it doesn't have the desired effect, any ideas?

August 30th, 2005, 09:12 AM

pabloj

Modding: Oracle MsSQL Firebird

Join Date: Jun 2001

Location: Outside US

Posts: 7,917

Time spent in forums: 2 Months 3 Weeks 3 Days 14 h 46 m 3 sec
Reputation Power: 279

sql Code:

Original - sql Code
update SJ5752.quadrat_dataplots set HEIGHT = '12.5', SPECIES = 'sg', STATE = 'l', MORPH = 'no' where PLOTID = 'plot 1';

UPDATE SJ5752.quadrat_dataplots 
SET HEIGHT = '12.5', SPECIES = 'sg', STATE = 'l', MORPH = 'no' 
WHERE PLOTID = 'plot 1';

Thread Tools	Search this Thread
Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread: