-2


i need help to create the right MySQL command.
As you can see in attachment ,
i need to copy string from column value with attribute_id 78 in column value with attribute_id 77 WHERE the entity_id is the same.

can anyone help me write the right code? Thanks in advance

screenshot sql tabble

Cookie Monster
  • 636
  • 1
  • 12
  • 29
Emanuele
  • 87
  • 1
  • 9

2 Answers2

0

This will do it:

UPDATE T
SET value = A.value
FROM
    YourTable T
    CROSS APPLY
    (
        SELECT TOP 1 value
        FROM YourTable
        WHERE
            [entity_id] = T.[entity_id]
            AND attribute_id = 78
    ) A
WHERE attribute_id = 77
Chris Mack
  • 5,148
  • 2
  • 12
  • 29
  • I have executed your query but I receive this error – Emanuele Dec 17 '17 at 12:21
  • UPDATE T SET value = A.value FROM 'mage_catalog_product_entity_varchar' T CROSS APPLY ( SELECT TOP 1 value FROM 'mage_catalog_product_entity_varchar' WHERE [entity_id] = T.[entity_id] AND attribute_id = 78 ) A WHERE attribute_id = 77 Messaggio di MySQL: Documentazione #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 'mage_catalog_product_entity_varchar' T CROSS APPLY ( ' at line 3 – Emanuele Dec 17 '17 at 12:22
  • Ah, you're using MySQL, not SQL Server. – Chris Mack Dec 17 '17 at 12:23
  • I'm sorry I messed up! – Emanuele Dec 17 '17 at 12:28
  • No problem, easy mistake - you're here to learn after all. :) – Chris Mack Dec 17 '17 at 13:56
0

This is based on that the OP is really using SQL Server (which I highly doubt, as they have tagged phpmyadmin).

WITH CTE AS (
    SELECT [value],
           LEAD([value]) OVER (PARTITION BY entity_id ORDER BY attribute_id) AS NextValue
    FROM YourTable
    WHERE attribute_id IN (77,78))
UPDATE CTE
SET value = NextValue
WHERE attribute_id = 77;
Thom A
  • 88,727
  • 11
  • 45
  • 75