Rotate Records to Update

[PCHomepage]

Doing this as an INSERT is easy enough but I can't seem to work out how to do it as an UPDATE for a single row of data that is currently in a single column. It needs to update only when there is a match between dcs_uploads.ID and dcs_temp.FileID. Any ideas?

UPDATE dcs_uploads SET
	(FileName, Segment1, Segment2, Segment3, Segment4, Segment5, Segment6, UploadDate)
	SELECT 
	MAX(filename) AS filename,
	MAX(chipname) AS chipname,
	MAX(lot) AS lot,
	MAX(fuseID) AS fuseID,
	MAX(sampleID) AS sampleID,
	MAX(datatype) AS datatype,
	MAX(expDesc) AS expDesc,
	MAX(date) AS date

	VALUES (SELECT
		CASE WHEN Column1 = 'filename' THEN Column2 ELSE NULL END AS filename,	
		CASE WHEN Column1 = 'chipname' THEN Column2 ELSE NULL END AS chipname,
		CASE WHEN Column1 = 'lot' THEN Column2 ELSE NULL END AS lot,
		CASE WHEN Column1 = 'fuseID' THEN Column2 ELSE NULL END AS fuseID,
		CASE WHEN Column1 = 'sampleID' THEN Column2 ELSE NULL END AS sampleID,
		CASE WHEN Column1 = 'datatype' THEN Column2 ELSE NULL END AS datatype,
		CASE WHEN Column1 = 'expDesc' THEN Column2 ELSE NULL END AS expDesc,
		CASE WHEN Column1 = 'date' THEN UNIX_TIMESTAMP(Column2) ELSE NULL END AS date
		FROM (SELECT FileID, Column1, Column2 FROM dcs_temp 
		WHERE Column1 NOT REGEXP ('[0-9]') 
		AND Column1 NOT LIKE 'QEDATA%' 
		AND Column1 NOT LIKE 'wavelength%' 
		AND Column1 NOT LIKE 'peak%'
	) AS s2 
) AS s1

 

[DonQuichote]

Look in the documentation for the INSERT ... ON DUPLICATE KEY statement.

+++ Despite being wrong in every important aspect, that is a very good analogy +++
Hex (in Darwin's Watch)

 

[PCHomepage]

Thank you, it looks like it should do the trick! However, it's not clear to me where I put the ON IF=$FileID clause to tell it which ID to use. Can you advise?

 

[PCHomepage]

Does this look about right? It gives no errors but neither does it seem to update. Do the aliases need to match the fields into which they are being inserted?

INSERT INTO dcs_uploads 
	(FileName, Segment1, Segment2, Segment3, Segment4, Segment5, Segment6, UploadDate)
	SELECT 
	MAX(lot) AS lot,
	MAX(filename) AS filename,
	MAX(chipname) AS chipname,
	MAX(fuseID) AS fuseID,
	MAX(sampleID) AS sampleID,
	MAX(datatype) AS datatype,
	MAX(expDesc) AS expDesc,
	MAX(date) AS date
	ON DUPLICATE KEY UPDATE ID = $FileID

	SELECT
	CASE WHEN Column1 = 'filename' THEN Column2 ELSE NULL END AS filename,	
	CASE WHEN Column1 = 'chipname' THEN Column2 ELSE NULL END AS chipname,
	CASE WHEN Column1 = 'lot' THEN Column2 ELSE NULL END AS lot,
	CASE WHEN Column1 = 'fuseID' THEN Column2 ELSE NULL END AS fuseID,
	CASE WHEN Column1 = 'sampleID' THEN Column2 ELSE NULL END AS sampleID,
	CASE WHEN Column1 = 'datatype' THEN Column2 ELSE NULL END AS datatype,
	CASE WHEN Column1 = 'expDesc' THEN Column2 ELSE NULL END AS expDesc,
	CASE WHEN Column1 = 'date' THEN UNIX_TIMESTAMP(Column2) ELSE NULL END AS date
	FROM (SELECT FileID, Column1, Column2 FROM dcs_temp 
	WHERE FileID = $FileID
) AS s2 
) AS s1