1

My problem is I got 2 table 1 is coach and 1 is tool

  • coach (cid, forename, surname, toolNo )
  • tool (toolNo , registNo )

I want to change the coach "Will Smith" 's toolNo to 10 by using the registNo. However my code change everyone's toolNo but not only will smith's one.

insert into coach values ('c2','will','smith',5)
insert into tool values ('10', 'R123')

    UPDATE coach
SET toolNo = t.toolNo 
From coach c,  tool t
WHERE t.registNo = 'R123'  
AND c.forename ='Will'
AND c.surname = 'smith';
Cœur
  • 37,241
  • 25
  • 195
  • 267
yoadle
  • 188
  • 1
  • 2
  • 12

1 Answers1

1

When joining in an UPDATE statement you only need list the tables to which you are joining in the FROM statement, but the table you are updating since that is already stated at the top of the query before SET.

Furthermore, according to this SO question you have to use the old-school method of joining without the ON clause. 

UPDATE coach
   SET coachtoolNo = t.toolNo 
  FROM tool t 
 WHERE t.registNo = 'R123'  
   AND coach.forename ='Will'
   AND coach.hsurname = 'smith';

What you have above in the question is a cross join between Coach and Tool, which is then cross joined again with Coach causing every record to update.

Community
  • 1
  • 1
JNevill
  • 46,980
  • 4
  • 38
  • 63
  • i have put a inner join and ON but it still change all of the coach's toolNo – yoadle Apr 10 '15 at 13:57
  • That's curious. I wish sqlfiddle.com would wake up so I could play a bit. The coffee hasn't kicked in yet, so just staring at the SQL isn't helping much. – JNevill Apr 10 '15 at 14:04
  • OK coffee is kicking in... Updating the answer now. – JNevill Apr 10 '15 at 14:13
  • That still might need to be tweaked a bit... but overall the idea is right. Read more on UPDATEs in Postgres at http://www.postgresql.org/docs/9.1/static/sql-update.html – JNevill Apr 10 '15 at 14:17
  • Yes and No... Looks like you cannot use an ON clause in a Postgres UPDATE stataement, but have to join the old school way. Some expert I turned out to be. I think I'll retire from SO for the day o_0 – JNevill Apr 10 '15 at 14:28
  • using the old school way doesnt have any affect – yoadle Apr 10 '15 at 15:10
  • 1
    i have solve it out when remove the coach.toolno = t.toolno in WHERE – yoadle Apr 10 '15 at 15:12
  • Oh thank goodness. I thought perhaps I'm losing my mind. I still think I'm losing it, but at least it's not negatively impacting your query any longer. – JNevill Apr 10 '15 at 15:13