I have the following query, but I'm afraid that INTO
it doesn't create temporary tables in MySQL , which in SQL server 2008 R2 it does without problems. I need to update a table from the data obtained from sr_itemRock
so that it is updated in sr_productos
. The detail that does not come out is the creation of temporary tables.
Query:
SELECT a.ItemID, a.ItemDesc
INTO #Temp
from sr_itemrock a
INNER JOIN sr_productos b
on a.ItemID = b.codigo
WHERE id > 600
UPDATE sr_productos
SET
descripcion_bra IN (SELECT ItemDesc FROM #Temp)
,descripcion_eng IN (SELECT ItemDesc FROM #Temp)
,descripcion_esp IN (SELECT ItemDesc FROM #Temp)
WHERE
ItemID IN (SELECT ItemID FROM #Temp)
DROP Table #Temp
This query normally in SQL Server does not have errors, but in MySQL it does. The detail is in the temporary tables. Can anyone give me any idea how to update data from one table to another using temporary tables?
Mistake:
[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 'from sr_itemrock a INNER JOIN sr_productos b on a.ItemID = b.codigo WHERE id ' at line 3
Since your question seems to focus on the first query, my answer is going to focus on that. But I mention in passing that
UPDATE
he doesn't seem to have the correct syntax for MySQL or SQL Server either.Indeed, the syntax
SELECT ... INTO ...
does not work in MySQL. In fact, the way to create temporary tables is also different.The equivalent of your SQL Server query:
...would be the following in MySQL:
Note that the syntax is
CREATE TEMPORARY TABLE nombre AS SELECT ...
, and that the name you assign to the table cannot start with a#
like you would in SQL Server.For the del
UPDATE
, the syntax in the clauseSET
doesn't seem valid on any database engine.I suspect this is the statement you're looking for: