SQL Update Query with INNER JOIN

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL Update Query with INNER JOIN

Author

Topic

velnias2010
Posting Yak Master

125 Posts

Posted - 2010-08-12 : 05:48:06

UPDATE xml_index AS x INNER JOIN [content] AS y ON x.content_id = y.content_id
SET folder_id = 4274
WHERE (x.xpath = '/root/department') AND x.value_string = 'Accounts')

But keeping getting error 'Incorrect syntax near 'AS'

Any help would be appreciated

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-08-12 : 06:00:02

UPDATE x
SET folder_id = 4274
from xml_index AS x
INNER JOIN [content] AS y ON x.content_id = y.content_id
WHERE (x.xpath = '/root/department') AND x.value_string = 'Accounts')

No, you're never too old to Yak'n'Roll if you're too young to die.

velnias2010
Posting Yak Master

125 Posts

Posted - 2010-08-12 : 06:03:12

I get invalid object X when I try to run that

velnias2010
Posting Yak Master

125 Posts

Posted - 2010-08-12 : 06:06:01

Another query I get a foreign key constraint when I try update a column in that DB anyway to get around that ?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-08-12 : 06:06:11

Then please give us table structure and your used query.

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-08-12 : 06:08:03

quote:
Originally posted by velnias2010

Another query I get a foreign key constraint when I try update a column in that DB anyway to get around that ?

Yes there is a way around that.
Make it correct.
If there is a foreign key constraint then you have to consider this.

No, you're never too old to Yak'n'Roll if you're too young to die.

velnias2010
Posting Yak Master

125 Posts

Posted - 2010-08-12 : 06:21:35

UPDATE [content]
SET folder_id = 4274
FROM [content] INNER JOIN
xml_index ON [content].content_id = xml_index.content_id
WHERE (xml_index.xpath = '/root/department') AND (xml_index.value_string = 'Accounts')

This my query but it went really wrong it updated every single record and ignored my "WHERE" statement why is this ?

velnias2010
Posting Yak Master

125 Posts

Posted - 2010-08-12 : 07:41:10

Hey guys I hate to bump but Im still stuck on this..I dont understand why the above query updates every single foler_id in that table and ignores the end of the query
"WHERE (xml_index.xpath = '/root/department') AND (xml_index.value_string = 'Accounts')"

Any help would be greatly appreciated

Kristen
Test

22859 Posts

Posted - 2010-08-12 : 08:05:05

"This my query but it went really wrong it updated every single record "

1. Use an ALIAS as recommended above:


UPDATE X
SET folder_id = 4274
FROM [content]AS X 
INNER JOIN ...

2. Use a SELECT first to check what is going to be updated!


-- UPDATE X
SET folder_id = 4274
-- SELECT TOP 100 *
-- SELECT COUNT(*)
FROM [content]AS X 
INNER JOIN ...

highlight from just after one of the "--" comments to the end and execute. So you can try the SELECT COUNT(*), the SELECT TOP 100 * and finally, once you are happy, the UPDATE X

Also, use a transaction:


BEGIN TRANSACTION
UPDATE X
SET folder_id = 4274
FROM [content]AS X 
INNER JOIN ...
...
-- COMMIT
-- ROLLBACK

after execution highlight, and execute, either the COMMIT and ROLLBACK. You can check whether the "(9,999 row(s) affected)" is "sensible", or you can do some SELECT TOP 100 * FROM [content] WHERE folder_id = 4274 ... or some other tests before using either COMMIT or ROLLBACK

velnias2010
Posting Yak Master

125 Posts

Posted - 2010-08-12 : 09:10:20

Grrrr...why is this INNER JOIN in an update so weird !!

Ok I ran the following

UPDATE x
SET x.folder_id = 16
FROM [content] AS x INNER JOIN
xml_index ON x.content_id = xml_index.content_id CROSS JOIN
x
WHERE (xml_index.xpath = '/root/department') AND (xml_index.value_string = 'Accounts')

And I got "Invalid object name x"

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-08-12 : 09:16:50

quote:
Originally posted by velnias2010

Grrrr...why is this INNER JOIN in an update so weird !!

Ok I ran the following

UPDATE x
SET x.folder_id = 16
FROM [content] AS x INNER JOIN
xml_index ON x.content_id = xml_index.content_id CROSS JOIN
x
WHERE (xml_index.xpath = '/root/department') AND (xml_index.value_string = 'Accounts')

And I got "Invalid object name x"

What is

CROSS JOIN x

doing in that query?

velnias2010
Posting Yak Master

125 Posts

Posted - 2010-08-12 : 09:17:49

I have no idea its putting that in automatically when I run the query !

Kristen
Test

22859 Posts

Posted - 2010-08-12 : 09:18:28

You can't do

CROSS JOIN x

can you? You need a "real" table in there, not an alias from elsewhere in the query I think?

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-08-12 : 09:19:17

quote:
Originally posted by velnias2010

I have no idea its putting that in automatically when I run the query !

How are you running the query? Not from SSMS?

Kristen
Test

22859 Posts

Posted - 2010-08-12 : 09:19:33

"I have no idea its putting that in automatically when I run the query !"

Run the query in SSMS Query Pane or somesuch then. Perhaps whatever you are using is getting in your way? (what are you using??)

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-08-12 : 09:19:38

What do you use to run the query???

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-08-12 : 09:20:19

No, you're never too old to Yak'n'Roll if you're too young to die.

velnias2010
Posting Yak Master

125 Posts

Posted - 2010-08-12 : 09:29:18

Im in SQL Server Management Studio and ran the query in SQL Plane

Kristen
Test

22859 Posts

Posted - 2010-08-12 : 09:51:25

Right click database

Select "New query"

Paste the code into the blank pane that opens

Is that what you are doing? or is it some other "SQL-Helper" type tool in SSMS perhaps

velnias2010
Posting Yak Master

125 Posts

Posted - 2010-08-12 : 09:53:50

I tried this
"Right click database

Select "New query"

Paste the code into the blank pane that opens"

And then the query worked fine.

Well it says

(0 row(s) affected)

(0 row(s) affected)

(1 row(s) affected)

(2 row(s) affected)

Why is it doing it 4 times ?

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-08-12 : 10:04:13

quote:
Originally posted by velnias2010

I tried this
"Right click database

Select "New query"

Paste the code into the blank pane that opens"

And then the query worked fine.

Well it says

(0 row(s) affected)

(0 row(s) affected)

(1 row(s) affected)

(2 row(s) affected)

Why is it doing it 4 times ?

Check if there are any triggers on the table [content]. Those may be results of the triggers.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources