BenD BenD - 3 months ago 14
MySQL Question

insert / update records from one table to another table, no clear join

I have a list of sku's in one table that I need to assign to product id's in another table the same way that one would in excel, by copying records from a column of sku's and pasting it next to the a column of product id's starting at the first row. I'd like to do this with an update query or other.

table1: tmp_pid
fields: pid, sku


This is where I have a random number of pid records. The sku field is empty. I'm trying to fill it with date from the next table.

table2: tmp_sku
fields: sku, used


This is where I keep a very long list of unique sku's and whether they have been used.

I tried this query but it does not work ([Err] 1054 - Unknown column 'tmp_sku.sku' in 'IN/ALL/ANY subquery')

UPDATE tmp_pid
SET tmp_pid.sku = tmp_sku.sku
WHERE tmp_sku.sku IN (SELECT sku FROM tmp_sku WHERE used = NO )


Table1 can have 20 or 1000 pid records, Table2 has 10000 unused sku's. I only need to copy the needed sku's next to the 20-1000 pid records in Table1. I know there is no connecting key between the two, but I am limited to this structure.

Answer

If I understand correctly, you want to get this result:

select p.*, s.sku
from (select p.*, (@rnp := @rnp + 1) as n
      from tmp_pid p cross join (select @rnp := 0) params
      order by pid
     ) p join
     (select s.*, (@rns := @rns + 1) as n
      from tmp_sku s cross join (select @rns := 0) params
      where used = 'NO'
      order by sku
     ) s
     on p.n = s.n;

If so, you can adapt this to an update:

update tmp_pid p join
       (select p.*, (@rnp := @rnp + 1) as n
        from tmp_pid p cross join (select @rnp := 0) params
        order by pid
       ) pp
       on p.pid = pp.pid join
       (select s.*, (@rns := @rns + 1) as n
        from tmp_sku s cross join (select @rns := 0) params
        order by sku
       ) s
       on pp.n = s.n
    set p.sku = s.sku;