CODEWITHSUNDEEP
sqlsybase
neo
neo

Reputation: 2471

SQL: Insert table -- column name not allowed

Ok, maybe a really beginner's question, I have the following table

table name: mytable, it has 2 columns: 'ID' and 'Value'.

Here's what I want to do. I want to go through 'mytable', find out entries where 'Value=x', and for those entries, I want to add another entry where ID equals the same ID and values=y.

I tried:

insert into mytable values (mytable.ID, 'y') where mytable.ID='x'

But I got an error: Only constants, constant expressions, or variables allowed here. Column names are illegal

I am using Sybase. what is the correct syntax to do this? thanks.

Upvotes: 0

Views: 2138

Answers (1)

Taryn
Taryn

Reputation: 247880

If you want to select from your table and then insert, then you will want to use an INSERT INTO..SELECT...FROM:

insert into mytable 
select a.ID, 'y'
from mytable a
where a.value='x';

See SQL Fiddle with Demo.

I am assuming that you meant the where clause to check where the value='x' not the id.

Upvotes: 5

Related Questions