lundi 27 mars 2017

SQL Update multiple columns if blank

I'm trying to create a SQL script in SSMS that will update 3 columns (name, suppliedid, color) based on what value is in 2 different columns (stripe, position) - BUT only if the 'name' and 'suppliedid' columns are blank (not NULL). Then duplicating that per row, from 001 for up to 1000.

I've got as far as updating the 3 columns based on what's in the in 2 columns, using this code:

UPDATE       keys
SET                name = '001', suppliedid = '001 RT', color = 'ffffff'
WHERE        (stripe = '0') AND (position = '0');

UPDATE       keys
SET                name = '002', suppliedid = '002 RT', color = 'ffffff'
WHERE        (stripe = '0') AND (position = '1');

I need to add the code to specify only update if 'name' and 'suppliedid' are blank.

I've tried this, but it doesn't work.

UPDATE       keys
SET                name = '001', suppliedid = '001 RT', color = 'ffffff'
WHERE        (stripe = '0') AND (position = '0') IF (name = '');

UPDATE       keys
SET                name = '002', suppliedid = '002 RT', color = 'ffffff'
WHERE        (stripe = '0') AND (position = '1') IF (name = '');

Here's a picture of the SQL table for reference. Click here

Any help would be very appreciated.

Thank you

Aucun commentaire:

Enregistrer un commentaire