3

How to write the Update with join in where clause

 3 years ago
source link: https://www.codesd.com/item/how-to-write-the-update-with-join-in-where-clause.html
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.

How to write the Update with join in where clause

advertisements

I am trying to run this SQL to update a table. The issue is that I need a join in order to update the correct record. This is what I have come up with so far:

Update UserProfile
  set UserProfile.PropertyValue = 'Test'
  join ProfilePropertyDefinition
      on ProfilePropertyDefinition.Id = UserProfile.PropertyDefinitionId
  where UserProfile.UserId = 11
  and ProfilePropertyDefinition.PortalID = 0
  and ProfilePropertyDefinition.PropertyName = 'Address1'
  and ProfilePropertyDefinition.PropertyCategory = 'Address'

This is the message I get:

Incorrect syntax near the keyword 'join'.

You are almost there, you forgot the from clause:

Update UserProfile
  set UserProfile.PropertyValue = 'Test'
  from UserProfile
  join ProfilePropertyDefinition
      on ProfilePropertyDefinition.Id = UserProfile.PropertyDefinitionId
  where UserProfile.UserId = 11
  and ProfilePropertyDefinition.PortalID = 0
  and ProfilePropertyDefinition.PropertyName = 'Address1'
  and ProfilePropertyDefinition.PropertyCategory = 'Address'

report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK