UPDATE a column to it's respective value.

[Zane]

I got a MySQL situation here that I'm wondering if it has a simple solution before I got all manual labor data entry on it.

 

At the moment, I have three tables...

 

- defects
   - id  (int)
   - name (varchar)

- pallets
    - id (int)
    -a bunch of other pallet related fields

- defected
     - id (int)
     - defect (int)
     - pallet (int)

 

As you can see, that third table is a joining table for pallets with multiple defects.  My problem now is that apparently I didn't have to go through all this multiple defect stuff because a pallet can only have one.  My question is...  Is there a simple query where I can

Select the pallet id  and defect id from defected... and use those to update a new defect column I'm gonna put into the pallets table?

 

 

[requinix]

FYI: you can do JOINs and such with UPDATE queries.

 

After making sure there is, in fact, only one defect per pallet (and after adding the defect name column to the pallets table), you can do a subquery. Not the most efficient thing ever, but it's quick to write, and it's not like you'll be doing this ever again so...

UPDATE pallets SET defect = (SELECT ...)

[Zane]

Yeah,.. I did think of that later.  Taking the first, last or middle as the default in the sql trickery .... process, but in the end I persuaded the client to delete all the entered items and start from scratch.  It seemed the easiest way to reset a database schema, especially when there were only 23 rows.