Help with SQL

Chefao

Member
Apr 30, 2017
45
27
Hello
my hotel contains many rare
and I wanted to leave all of them with allow_trade 0 from another table (catalog_items)

tables: furniture and catalog_items

I want the allow_trade to stay 0 from catalog_items

I tried this:
SELECT item_id
FROM catalog_items
INNER JOIN furniture ON catalog_items.item_id = furniture.allow_trade

UPDATE db.furniture SET allow_trade = '0' WHERE catalog_items.item_id = 915



Can someone help me?
 
Last edited:

JayC

Always Learning
Aug 8, 2013
5,493
1,398
I don't understand your first query. You are inner joining where catalog_items ITEM ID = Furniture Allow Trade

Why not just run this query:
UPDATE furniture SET allow_trade = '0' WHERE is_rare = '1'

Or if you really need catalog items:
UPDATE furniture
SET furniture.allow_trade = '0'
FROM furniture INNER JOIN catalog_items ON catalog_items.item_id = furniture.id AND catalog_items.[row]=[value]
Change row and value to whatever you are looking for in the catalog items i guess?
 

Chefao

Member
Apr 30, 2017
45
27
#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 'FROM furniture INNER JOIN catalog_items ON catalog_items.item_id = furniture.id ' at line 3


I'll explain better
I want all the items that are on a page
stay with allow_trade 0

It seems that catalog_items is difficult, so would be

furniture = allow_trade 0
of the table catalog_pages = page_id = 500

This code is very complicated
but need the inner join?
 

Queso

echo 'Web Developer';
Nov 29, 2016
233
72
#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 'FROM furniture INNER JOIN catalog_items ON catalog_items.item_id = furniture.id ' at line 3


I'll explain better
I want all the items that are on a page
stay with allow_trade 0

It seems that catalog_items is difficult, so would be

furniture = allow_trade 0
of the table catalog_pages = page_id = 500

This code is very complicated
but need the inner join?
This should give you all the data from furniture that match the page_id 500
Code:
SELECT * FROM furniture WHERE id IN (SELECT item_id FROM catalog_items WHERE page_id = 500)
 

JayC

Always Learning
Aug 8, 2013
5,493
1,398
update furniture
set furniture.allow_trade = '0'
from furniture inner join catalog_items
on furniture.ID = catalog_items.item_id
and catalog_items.PageID = '0'
 

Chefao

Member
Apr 30, 2017
45
27
none of the codes worked
I need the allow_trade to be 0
from catalog_items
I would not need to go one by one just grab the id of the items and run the sql
I have more than a thousand rare ones in the catalog

the code should look like this: (example)


DELETE items.* FROM items INNER JOIN `users` ON items.user_id = users.id INNER JOIN `furniture` ON items.base_item = furniture.id INNER JOIN `catalog_items` ON catalog_items.item_id = items.base_item WHERE users.username = 'user' AND catalog_items.page_id IN (950,982, 3652234, 960, 857, 3652255, 3652236, 7857843, 156 , 7857848, 51, 16, 3652248, 989, 986, 988, 3652251, 874, 3652235, 3652247, 95, 987)
?

furniture:
rdRsMVA.png


catalog_items:

TxgNAm3.png
 

Queso

echo 'Web Developer';
Nov 29, 2016
233
72
none of the codes worked
I need the allow_trade to be 0
from catalog_items
I would not need to go one by one just grab the id of the items and run the sql
I have more than a thousand rare ones in the catalog

the code should look like this: (example)


DELETE items.* FROM items INNER JOIN `users` ON items.user_id = users.id INNER JOIN `furniture` ON items.base_item = furniture.id INNER JOIN `catalog_items` ON catalog_items.item_id = items.base_item WHERE users.username = 'user' AND catalog_items.page_id IN (950,982, 3652234, 960, 857, 3652255, 3652236, 7857843, 156 , 7857848, 51, 16, 3652248, 989, 986, 988, 3652251, 874, 3652235, 3652247, 95, 987)
?

furniture:
rdRsMVA.png


catalog_items:

TxgNAm3.png
Honestly, what you're asking isn't making any sense. You're asking for us to give you a query to display all rares, then you're giving us an example of a DELETE query, you're not making any sense legit. I gave you a code to display all the items from the furniture table which are listed by the page_id, because you said you wanted to see the furniture data of all the items in page id 500. I am actually really confused what you're trying to do, the query I gave you shows every item in a certain page id which you can change from 500 to whatever inside the furniture table. Now the way you're explaining it, it sounds like you want to make all the items in a certain page of the catalog nontransferable, correct? So, what you would do is run that query and change all the allow_trade for those items to 0, simple. If you're saying you can't do it because you have to do it one by one, use Navicat, because i'm unsure why you're using XAMPP in 2018, unless it's localhost, then understandable.
 

Chefao

Member
Apr 30, 2017
45
27
navicat does not have this function
I use cpanel and emulator in vps


I meant
in the table catalog pages have a correct id?
Does the catalog item need the correct page ID?
so if I browse the items you have inside a page_id will appear
I wanted everyone to have the allow_trade 0
I have no idea what the code is
I also do not know if I can create it

I'll give an example:

UPDATE `furniture` SET allow_trade 0 WHERE catalog_items.item_id = (950, 801, 841, 814)

Can it be something like this?
 

Users who are viewing this thread

Top