SitePoint Sponsor

User Tag List

Results 1 to 9 of 9

Hybrid View

  1. #1
    SitePoint Evangelist
    Join Date
    Nov 2003
    Location
    England
    Posts
    540
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Inner join on update?

    Hey all,

    Im just wondering if its possible to do an INNER JOIN while using an UPDATE statement, something like the following:

    Code:
        UPDATE
            Description d
        SET
            d.Text = "Hello World"
        INNER JOIN
            Item i ON d.ItemID = i.ItemID
        WHERE
            i.ImportID = 5
    Basically, I have something like the following:

    Item
    ----------
    ItemID
    ImportID

    Description
    -----------
    DescriptionID
    ItemID
    Text

    I have an importID, and I want to update the description of the item with this importID.

    Am I on the right track?

    Thanks,
    Matt.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,336
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    yes, you can do this, but the syntax is different from what you have

    it's under the UPDATE statement in the manual, near the bottom
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Evangelist
    Join Date
    Nov 2003
    Location
    England
    Posts
    540
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK - Thanks Rudy

  4. #4
    SitePoint Evangelist
    Join Date
    Nov 2003
    Location
    England
    Posts
    540
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Is this correct? its based on the syntax I saw on the site, but theres alot of people complaining of incorrect updates and showing their code also

    So just to check:

    Code:
    	UPDATE
    		Description d
    	INNER JOIN
    		Item i ON d.ItemID = i.ItemID
    	SET
    		d.Text = "Hello World"
    	WHERE
    		i.ImportID = 5
    Thanks,
    Matt.

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,336
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    use single quotes around string values
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    Tranceoholic lilleman's Avatar
    Join Date
    Feb 2004
    Location
    Írebro, Sweden
    Posts
    2,716
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    Quote Originally Posted by r937
    use single quotes around string values
    Is there a specific reason to why it's better? Just curious, not trying to prove you wrong or something like that.
    ERIK RIKLUND :: Yes, I've been gone quite a while.

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,336
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    because in many databases, "foo" is a column name, while 'foo' is a string
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    Tranceoholic lilleman's Avatar
    Join Date
    Feb 2004
    Location
    Írebro, Sweden
    Posts
    2,716
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    Quote Originally Posted by r937
    because in many databases, "foo" is a column name, while 'foo' is a string
    I see. Thanks for the explanation.
    ERIK RIKLUND :: Yes, I've been gone quite a while.

  9. #9
    SitePoint Evangelist
    Join Date
    Nov 2003
    Location
    England
    Posts
    540
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    So apart from that its fine I guess?

    I've always used single quotes with SQL before - I didn't even know you could use double quotes, don't know why i've used double on this thread!


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •