New SQL question.

[Ngreth Thergn]

I ahve the following query that works...

SELECT items.item_name, recipes.*
FROM `recipes`, `items`
WHERE items.item_id = recipes.item_id
AND (recipes.tradeskill = 'Tailoring' OR recipes.tradeskill = 'Smithing')
AND items.item_name LIKE "%of the skeptic"

I want to alter that to do an UPDATE on the recipes table and

SET subcat = 'agnostic'

can this be done with the cross table nature of it? or do I need to make a script and just get a list of recipe ID's and then run the update on them?

[Neebat]

SELECT items.item_name, recipes.*
FROM `recipes`, `items`
WHERE items.item_id = recipes.item_id
AND (recipes.tradeskill = 'Tailoring' OR recipes.tradeskill = 'Smithing')
AND items.item_name LIKE "%of the skeptic"

I'm a gnome, so remember what happened to Luclin after we moved the tinkering headquarters to Shadow Haven.

UPDATE recipes r SET r.subcat = 'agnostic'
WHERE (r.tradeskill = 'Tailoring' OR r.tradeskill = 'Smithing')
AND r.item_id in (
    SELECT i.item_id FROM items i WHERE i.item_name LIKE "%of the skeptic"
)

You've gotten better at SQL (249)!

A true expert wouldn't need subselects. For older versions of MySQL, and other implementations without subselects, you may need a temp table.

[Ngreth Thergn]

I am game... but.... I think that this version of SQL cannot take sub-selects :/

[Ngreth Thergn]

yup it choked on it.

#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 'SELECT i.item_id FROM items i WHERE i.item_name LIKE "%of the s

so, looks like I get to write a script

Thanks for trying!

[Neebat]

From The MySQL news it looks like MySQL has been handling subselects for a couple years now.

If you've got 4.1 or higher of MySQL, the problem is likely some oddity of the parser that I'm not familiar with. For instance, I notice you quote table names. I've never had to do that. It might help.

Otherwise, I'm sorry to say a script might be best.

[Britneyy]

what MySQL ver are you running?

[Ngreth Thergn]

MySQL 4.0.22-standard

and curently it is a shared server, so I cannot get it changed.

[KyrosKrane]

Two quick notes. First, you cannot use IN clauses with MySQL 4.0.22 (as you've already noted). I think IN clauses were introduced with 4.1, but I'm not positive.

Second, quoting table and field names is optional, but if you do it, you must use the back-quote ` (usually under the tilde, at the top left of the keyboard), NOT the regular apostrophe. I've noticed a lot of times (not in this thread) when people post SQL to help Ngreth, they use the wrong quote sign, and that breaks the code.

[Neebat]

Two quick notes. First, you cannot use IN clauses with MySQL 4.0.22 (as you've already noted). I think IN clauses were introduced with 4.1, but I'm not positive.

You're correct. "IN clauses" are one of the forms of subselects. I wanted to be sure I was giving accurate infromation, so I did a websearch for MySQL subselects. The link I gave above shows that version 4.1 added subselects. (From what I can tell, that includes IN clauses in all reasonable locations.) Since 4.1 has been out for over 2 years, I had some hope that wasn't the problem. Prior to 4.1, MySQL was considered incomplete to the point of unusable by a lot of folks. With that release, it became a viable alternative for large scale projects.

Second, quoting table and field names is optional, but if you do it, you must use the back-quote ` (usually under the tilde, at the top left of the keyboard), NOT the regular apostrophe. I've noticed a lot of times (not in this thread) when people post SQL to help Ngreth, they use the wrong quote sign, and that breaks the code.

I avoided the quotes because they looked out of place to me. As far as I know, the only time they're required is table and column names with non-alphanumeric characters. I offered the possibility of adding quotes because I'm always ready for an unfamiliar db engine to do strange things.

Sorry, Ngreth. I don't know any way to do your Update in SQL alone with that old MySQL.

[KyrosKrane]

Ngreth, try this. According to the SQL docs, this should work. (I'm assuming the "subcat" column only exists in the recipes table, not in items.)

UPDATE items, recipes
SET subcat = 'agnostic'
WHERE items.item_id = recipes.item_id
AND (recipes.tradeskill = 'Tailoring' OR recipes.tradeskill = 'Smithing')
AND items.item_name LIKE "%of the skeptic"

Edit: The reference from the MySQL docs on UPDATE:

Starting with MySQL 4.0.4, you can also perform UPDATE operations that cover multiple tables. The table_references part lists the tables involved in the join. Its syntax is described in Section 13.1.7.1, “JOIN Syntax”. Here is an example:

UPDATE items,month SET items.price=month.price
WHERE items.id=month.id;

The example shows an inner join using the comma operator, but multiple-table UPDATE statements can use any type of join allowed in SELECT statements, such as LEFT JOIN.

[Ngreth Thergn]

Ngreth, try this. According to the SQL docs, this should work. (I'm assuming the "subcat" column only exists in the recipes table, not in items.)

UPDATE items, recipes
SET subcat = 'agnostic'
WHERE items.item_id = recipes.item_id
AND (recipes.tradeskill = 'Tailoring' OR recipes.tradeskill = 'Smithing')
AND items.item_name LIKE "%of the skeptic"

Edit: The reference from the MySQL docs on UPDATE:

looks good (I will not be trying this today, to dangerous for my ultra busy work day (every wednesday))

but to be more clear could I not do:

UPDATE items, recipes
SET recipes.subcat = 'agnostic'
WHERE items.item_id = recipes.item_id
AND (recipes.tradeskill = 'Tailoring' OR recipes.tradeskill = 'Smithing')
AND items.item_name LIKE "%of the skeptic"

Subcat is unique. but I was wondering if this would deal with the need for it to be unique?

[KyrosKrane]

Heh. I misread your post and thought you said the second one didn't work, then spent a goodly bit of time trying to find the problem. =) /bonk Kyros

Yes, your change should in theory deal with non-unique column names. But again, I haven't tested it myself.

[Neebat]

Thank you! I really believed you couldn't do an update across two tables, and now I know better.

Guess that shows how much SQL I really know.