MYSQL query check if ID exist and copy column row values to another table related column names

user2114900 :

I am trying to save Mysql row data from one database table and copy values in new database table via PhpMyAdmin but there is an issue.

My lack of knowledge is result for asking advanced users for help here. Copy, join, merge, delete or else :D ..i am really not sure what is the best method to solve this.

TABLE 1 (old) has columns: id, product_id, identifier, content

id - we dont need it for this query
post_id - (INT) which is actually related to ID in TABLE 2
identifier - (VARCHAR) different row values such as quantity, color, discount, price1, price2 which are repeatable fields
content - *(LONGTEXT) content

TABLE 2 (new) has columns:

id - (INT)
quantity - (VARCHAR)
color - (VARCHAR)
discount - (VARCHAR)
price1 - (VARCHAR)
price2 - (VARCHAR)

I need to check with mysql query does id in TABLE 2 exist compared with post_id from TABLE 1.

If does not exist skip to another record.

If exist then from TABLE 1 column called "identifier" check record names/values in rows such as quantity, color, discount, price1, price2 and copy content column values to TABLE 2 columns (names related - finded in rows of TABLE 1 column identifier.)

To simplify...Check ID from TABLE 1. If ID is good use identifier value and copy CONTENT column value from TABLE 1 to related ID and column name in TABLE 2.

GMB :

You can pivot the source EAV table in a subquery using conditional aggregation, then join it with the target table for update. coalesce() can be used to handle missig attributes in the source table.

update table2 t2
inner join (
    select 
        post_id,
        max(case when identifier = 'quantity' then content end) quantity,
        max(case when identifier = 'color' then content end) color,
        max(case when identifier = 'discount' then content end) discount,
        max(case when identifier = 'price1' then content end) price1,
        max(case when identifier = 'price2' then content end) price2
    from table1 
    group by post_id
) t1 on t1.post_id = t2.id
set 
    t2.quantity = coalesce(t1.quantity, t2.quantity),
    t2.color    = coalesce(t1.color,    t2.color)
    t2.discount = coalesce(t1.discount, t2.discount)
    t2.price1   = coalesce(t1.price1,   t2.price1)
    t2.price2   = coalesce(t1.price2,   t2.price2)

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at2020-04-12

Comments

0 comments

TOP Ranking

Article

MYSQL query check if ID exist and copy column row values to another table related column names

MYSQL query check if ID exist and copy column row values to another table related column names

Can't pre-populate phone number and message body in SMS link on iPhones when SMS app is not running in the background

pump.io port in URL

Failed to listen on localhost:8000 (reason: Cannot assign requested address)

Unable to use switch toggle for dark mode in material-ui

grouping by column variables and appending a new variable based on condition

BigQuery - concatenate ignoring NULL

Spring Boot JPA PostgreSQL Web App - Internal Authentication Error

ngClass error (Can't bind ngClass since it isn't a known property of div) in Angular 11.0.3

Python Read Directory And Output to CSV

Remove adjacent duplicates in linked list in C

Angular 8. Unknown amount of http.get requests in array to call, must be sequential, what to use

How to keep curl session alive between two php processes?

Limit number of characters in uitextview

JMeter: Why get error when try to save test plan

Always setting the text cursor on Textbox

MTKView Displaying Wide Gamut P3 Colorspace

Vector input in shiny R and then use it

How to implement an authentication method using Spring Boot and JPA?

Laravel getting value from another table using eloquent

org.springframework.web.client.HttpClientErrorException: 401 null

When I click any button in my view page the form is submitted