PHP, SQL - INSERT INTO SELECET CASE-CodePudding

I have this PHP code connected with form (using nette framework), name of columns are different in every table but geo column is in everyone:

                $this->masterRepository->query("

                INSERT INTO ".$table." (".$cols.")
                    SELECT ".$cols."
                        CASE
                            WHEN `geo` = '".$values['old_text']."' THEN `geo` = '".$values['new_text']."'
                            ELSE `geo` = '".$values['new_text']."'
                        END
                    FROM ".$table." WHERE `geo` = '".$values['old_text']."';

                ");

which generate this SQL:

INSERT INTO some_table (num_order, geo, url, note)
SELECT num_order, geo, url, note
 CASE
 WHEN `geo` = 'US' THEN `geo` = 'CA'
 ELSE `geo` = 'CA'
 END
FROM some_table 
WHERE `geo` = 'US';

but I'm getting this error:

SQLSTATE[42000]: Syntax error or access violation: 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 'CASE
                            WHEN `geo` = 'US' THEN `geo` = 'CA'

The goal is to COPY data into the same table but change value of geo column before insert. I know that ELSE value looks stupid but CASE will always end up same because the WHERE selection.

How can I fix this error or should I use different approach? Thanks.

CodePudding user response：

Your alias is wrong note should come after case and there is no use of where as you are handling the geo values using CASE

INSERT INTO some_table (num_order, geo, url, note)
SELECT num_order, geo, url, 
CASE
WHEN `geo` = 'US' THEN `geo` = 'CA'
ELSE `geo` = 'CA'
END as note 
FROM some_table

CodePudding user response：

The syntax error is because you're missing a comma before CASE. But there are other problems with the query after fixing that.

You don't need the CASE expression at all, since the WHERE clause is only selecting those rows. What you want is:

INSERT INTO some_table (num_order, url, note, geo)
SELECT num_order, url, note, 'CA'
FROM some_table 
WHERE `geo` = 'US';

To do this with your variables, you'll need to remove geo from $cols so that SELECT ".$cols will not include it and you can add $values["new_text"] at the end of the SELECT list.

$cols1 = str_replace('geo,', '', $cols);
$this->masterRepository->query("

    INSERT INTO ".$table." (".$cols1.", geo)
    SELECT ".$cols1.", '".values['new_text']."'
    FROM ".$table." 
    WHERE `geo` = '".$values['old_text']."';

");