CODEWITHSUNDEEP
mysql
kyuzon
kyuzon

Reputation: 45

Using Select query to create an update script in mysql, syntax question?

so in oracle sql if i wanted to generate a script to do some updates to run later i could run query like this

SELECT 'UPDATE p_information SET admin_entitiy_id ='||asa.id||' where pi.id 
='||PI.id||';'
FROM
p_information pi
LEFT JOIN admin_setup_accounts asa ON asa.name = pi.admin_entity_unverified
WHERE
pi.admin_entity_unverified IS NOT NULL
AND pi.admin_entity_unverified <> ''
AND pi.admin_auditing_entity_id IS NULL
and pi.id < 200000
AND pi.admin_entity_unverified IN (select name  FROM admin_setup_accounts)

that would give me output list of update statements with the proper asa.id and pi.id from the overall query, but seems syntax doesnt work in mysql. Is something like this possible in mysql, or is there some other way to do this ? main reason is im looking for way to generate backup script so after any datafix would be able to go back if needed among other reasons.

thanks

Upvotes: 0

Views: 706

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522516

MySQL does not normally use || for concatenation, unless you have turned on the ANSI pipe mode. Instead, try using the CONCAT function:

SELECT CONCAT('UPDATE p_information SET admin_entitiy_id = ', asa.id,
              ' WHERE pi.id = ', PI.id, ';')
FROM p_information pi
LEFT JOIN admin_setup_accounts asa
    ON asa.name = pi.admin_entity_unverified
...

Note that there might be a way to go about doing this without generating text update statements, but instead writing a direct update.

Upvotes: 2

Related Questions