CODEWITHSUNDEEP
c#sqlpostgresql
Tetragrammaton
Tetragrammaton

Reputation: 71

Postgres SQL Insert Command combined with WHERE

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.

Upvotes: 0

Views: 76

Answers (2)

Vasyl Moskalov
Vasyl Moskalov

Reputation: 4630

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?

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269953

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' + "'";

Upvotes: 0

Related Questions