Postgres SQL Insert Command combined with WHERE

Question

I thought this would be easy, but I can't make it work:

Table=(int,int,text)

NpgsqlCommand command = new NpgsqlCommand("select * from database_data ncd inner join players p on p.player_id = ncd.player_id where p.playername ='Name1', conn);

string _data = (String)command.ExecuteScalar();

now I got the data....NOW I just wanna write it back...but...

NpgsqlCommand command2 = new NpgsqlCommand("Update database_data ncd inner join players p on p.player_id = ncd.player_id where p.playername ='Name2' + "'", conn);
command2.Parameters.Add(?????)
command2.ExecuteNonQuery();

I only managed to update the whole row.

NpgsqlCommand command2 = new NpgsqlCommand("insert into database_data values(1,2222,3333)", conn);
command2.ExecuteNonQuery();
conn.Close();

This works nicely, but I cannot combine it with the join and where.

If join doesn't work, I can simply work with where player_id=x and get the player_id somewhere else.

Vasyl Moskalov · Accepted Answer · 2016-03-15 12:09:31Z

1

From my best knowledge update statement in pqsql should look like

update <table> set field1=value1, field2=value2 .... from FROM_CLAUSE WHERE WHERE_CLAUSE

But in your piece of code your statement did not match with this template. So, could you please tell more precisely what you want to do with data?

answered Mar 15, 2016 at 12:09

Vasyl Moskalov

4,6403 gold badges24 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Tetragrammaton Over a year ago

havn't checked the answers yet....my intent is to write a new value into the field..I see I messed up the copy and paste in the example...I will fix that first

Tetragrammaton Over a year ago

So I edited the question...the first Readstuff works...I just wanna do the same and write the data back....but when I add VALUE into the statement it doesn't work ....so again FIRST STRING works!!

Tetragrammaton Over a year ago

"update database_data set data='teeeest' where player_id=17" this one works so far.....thx for the set....

Gordon Linoff · Accepted Answer · 2016-03-15 12:09:06Z

0

This syntax is going to fail in Postgres unless you have a table called ncd.data:

Update ncd.data 
    from data ncd inner join
         players p
         on p.player_id = ncd.player_id
    where p.playername ='Name2' + "'";

I am guessing your intention is:

Update data ncd
    from players p
    where p.player_id = ncd.player_id and
          p.playername ='Name2' + "'";

answered Mar 15, 2016 at 12:09

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

5 Comments

Tetragrammaton Over a year ago

yes I have a table database_data(edited it for better understanding) ...as said my first example works.... "update database_data set data='teeeest' where player_id=17"...this one works too....now I try and combine it with: ncd inner join players p on p.player_id = ncd.player_id where p.playername ='Name1' so I don't need to get the id in a seperate statement

Tetragrammaton Over a year ago

just to make clear: this one work for reading: select * from database_data ncd inner join players p on p.player_id = ncd.player_id where p.playername ='Name1'

Tetragrammaton Over a year ago

I just used two statements.... ID=select player_id from players where playername='Name' update database_data set data = '45645' where player_id =ID

Gordon Linoff Over a year ago

@Tetragrammaton . . . Are you saying that the second query in this answer did not work? If so, I'll just delete the answer.

Tetragrammaton Over a year ago

yes.....I JUST need to combine these two...just for curiosity: ++ID=select player_id from players where playername='Name'++ ++update database_data set data = '45645' where player_id =ID++ these two work in 2 "lines"...

Collectives™ on Stack Overflow

Postgres SQL Insert Command combined with WHERE

2 Answers 2

3 Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related