MySQL SQL Error more then 1 table select

Question

I am trying to write an sql that checks up multiple tables,this is what I got:

UPDATE item_template SET BuyPrice = 0, SellPrice = 1 WHERE entry IN (SELECT item FROM npc_vendor WHERE entry IN (SELECT entry FROM creature WHERE area = 3998)));

Now my error is:

[Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 1

What may cause it?

Ike Walker · Accepted Answer · 2014-05-01 12:48:42Z

1

As Dave Mroz mentioned, this query would be much more efficient if you used multi-table update syntax with JOIN, something like this:

UPDATE item_template 
  inner join npc_vendor on npc_vendor.item = item_template.entry
  inner join creature on creature.entry = npc_vendor.entry
SET item_template.BuyPrice = 0, item_template.SellPrice = 1 
where creature.area = 3998;

answered May 1, 2014 at 12:48

Ike Walker

65.8k14 gold badges115 silver badges112 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

bthe0 Over a year ago

Great,I just tested mine and it took more then 300 seconds to run.I am pretty novice into mysql :)

Dave Mroz · Accepted Answer · 2014-05-01 12:42:56Z

1

You have two opening ( and three closing )

Also, you should probably try to rewrite this with a JOIN instead of nested subqueries.

answered May 1, 2014 at 12:42

Dave Mroz

1,4992 gold badges14 silver badges27 bronze badges

Comments

user3470953 · Accepted Answer · 2014-05-01 12:43:07Z

1

it should be

UPDATE item_template SET BuyPrice = 0, SellPrice = 1 WHERE entry IN (SELECT item FROM npc_vendor WHERE entry IN (SELECT entry FROM creature WHERE area = 3998));

answered May 1, 2014 at 12:43

user3470953

11.4k2 gold badges19 silver badges18 bronze badges

1 Comment

bthe0 Over a year ago

Thank you,seems I have to drink another coffee.

picus · Accepted Answer · 2014-05-01 12:43:17Z

1

I think you have 1 too many closing parens.

answered May 1, 2014 at 12:43

picus

1,5372 gold badges14 silver badges23 bronze badges

Comments

Nagaraj S · Accepted Answer · 2014-05-01 12:44:07Z

1

you have one extra closing paranthese

    UPDATE item_template
SET BuyPrice = 0,
    SellPrice = 1
WHERE entry IN
    (SELECT item
     FROM npc_vendor
     WHERE entry IN
         (SELECT entry
          FROM creature
          WHERE area = 3998));
                         ^^^^^^^ Remove one ) here

answered May 1, 2014 at 12:44

Nagaraj S

13.5k6 gold badges35 silver badges53 bronze badges

Comments

Nagaraj S · Accepted Answer · 2014-05-01 12:45:46Z

1

UPDATE item_template SET BuyPrice = 0, SellPrice = 1 WHERE entry IN (SELECT item FROM npc_vendor WHERE entry IN (SELECT entry FROM creature WHERE area = 3998));

this is correct.you have added extra ')'

edited May 1, 2014 at 12:45

Nagaraj S

13.5k6 gold badges35 silver badges53 bronze badges

answered May 1, 2014 at 12:43

ristheeka

51 bronze badge

Collectives™ on Stack Overflow

MySQL SQL Error more then 1 table select

6 Answers 6

1 Comment

Comments

1 Comment

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

1 Comment

Comments

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related