Urgent Help ...for a mySQL update query

Developers Databases
#1

Hi all,

I’m trying to code an update query . The concept is that I have to copy some fields values from previous year to actual year checking the fields do not overwrite them.
If I use it for a single field it works but I would like to use it checking for all fields the same where condition:
WHERE a.FIELD is null or a.FIELD = 0 or a.FIELD = ‘’
in order to avoid to write over possible existing values of actual year.
The problem is that update accept only a final where condition and I don’t want if it’s possible to make 10 queries for ten field for thousand of records .
Is there an “easy” workaround ? Could you write a sample from my data ?

UPDATE
members a
INNER JOIN member_yearprev00 b ON a.national_federationID = b.national_federationID AND a.year_card_actual = b.year_card_actual + 1
SET
a.section_ID = b.section_ID
WHERE a.section_ID is null or a.section_ID = 0 or a.section_ID = ‘’,
…
a.fiscalcode = b.fiscalcode
WHERE a.fiscalcode is null or a.fiscalcode = 0 or a.fiscalcode = ‘’

#2

WHERE (a.section_ID is null or a.section_ID = 0 or a.section_ID = ‘’) OR (a.fiscalcode is null or a.fiscalcode = 0 or a.fiscalcode = ‘’) OR … and so on.

#3

Thanks !! Which could be more efficient from DB side ??
I just now tried also this one:

UPDATE
members a
INNER JOIN member_yearprev00 b ON a.national_federationID = b.national_federationID AND a.year_card_actual = b.year_card_actual + 1
SET
a.section_ID = IF(a.section_ID is null or a.section_ID =’’ or a.section_ID = 0, b.section_ID, a.section_ID),
a.fiscalcode = IF(a.fiscalcode is null or a.fiscalcode =’’ or a.fiscalcode = 0, b.fiscalcode, a.fiscalcode)

#4

Not able to copy data from one year to other one

Hi,
it work but I got a strange problem.
imagine to have 3 i.e. records 1 per year with the same member name and code . During years I need to inherited only values of last year if field of actual year are empty.

I’ve attached a image that explains better than my words…

This is the code I’m using
members are main table (I have to update) and yearprev00 is the temp-copy where I post previous years data with another insert into.

“UPDATE
members a
INNER JOIN member_yearprev00 b ON a.national_federationID = b.national_federationID AND a.year_card_actual = b.year_card_actual + 1
SET
a.section_ID = IF(a.section_ID is null or a.section_ID = ‘’ or a.section_ID = 0 , b.section_ID , a.section_ID),
a.userName = IF(a.userName is null or a.userName = ‘’ or a.userName = 0 , b.userName , a.userName),
a.titleID = IF(a.titleID is null or a.titleID = ‘’ or a.titleID = 0 , b.titleID , a.titleID),
a.borncountry = IF(a.borncountry is null or a.borncountry = ‘’ or a.borncountry = 0 , b.borncountry , a.borncountry),
a.fax = IF(a.fax is null or a.fax = ‘’ or a.fax = 0 , b.fax , a.fax),
a.mobile02 = IF(a.mobile02 is null or a.mobile02 = ‘’ or a.mobile02 = 0 , b.mobile02 , a.mobile02),
a.addressLine2 = IF(a.addressLine2 is null or a.addressLine2 = ‘’ or a.addressLine2 = 0 , b.addressLine2 , a.addressLine2),
a.fbname = IF(a.fbname is null or a.fbname = ‘’ or a.fbname = 0 , b.fbname , a.fbname),
a.twname = IF(a.twname is null or a.twname = ‘’ or a.twname = 0 , b.twname , a.twname),
a.skype = IF(a.skype is null or a.skype = ‘’ or a.skype = 0 , b.skype , a.skype),
a.country = IF(a.country is null or a.country = ‘’ or a.country = 0 , b.country , a.country),
a.vatcode = IF(a.vatcode is null or a.vatcode = ‘’ or a.vatcode = 0 , b.vatcode , a.vatcode),
a.fiscalcode = IF(a.fiscalcode is null or a.fiscalcode = ‘’ or a.fiscalcode = 0 , b.fiscalcode , a.fiscalcode),
a.medical_date = IF(a.medical_date is null or a.medical_date = ‘’ or a.medical_date = 0 , b.medical_date , a.medical_date),
a.subscr_date = IF(a.subscr_date is null or a.subscr_date = ‘’ or a.subscr_date = 0 , b.subscr_date , a.subscr_date),
a.approval_date = IF(a.approval_date is null or a.approval_date = ‘’ or a.approval_date = 0 , b.approval_date , a.approval_date),
a.shirt_name = IF(a.shirt_name is null or a.shirt_name = ‘’ or a.shirt_name = 0 , b.shirt_name , a.shirt_name),
a.shirt_number = IF(a.shirt_number is null or a.shirt_number = ‘’ or a.shirt_number = 0 , b.shirt_number , a.shirt_number),
a.shirt_size = IF(a.shirt_size is null or a.shirt_size = ‘’ or a.shirt_size = 0 , b.shirt_size , a.shirt_size),
a.pants_size = IF(a.pants_size is null or a.pants_size = ‘’ or a.pants_size = 0 , b.pants_size , a.pants_size),
a.helmet_size = IF(a.helmet_size is null or a.helmet_size = ‘’ or a.helmet_size = 0 , b.helmet_size , a.helmet_size),
a.boots_size = IF(a.boots_size is null or a.boots_size = ‘’ or a.boots_size = 0 , b.boots_size , a.boots_size),
a.gloves_size = IF(a.gloves_size is null or a.gloves_size = ‘’ or a.gloves_size = 0 , b.gloves_size , a.gloves_size),
a.attachment_doc = IF(a.attachment_doc is null or a.section_ID = ‘’ or a.attachment_doc = 0 , b.attachment_doc , a.attachment_doc),
a.attachment_img = IF(a.attachment_img is null or a.section_ID = ‘’ or a.attachment_img = 0 , b.attachment_img , a.attachment_img)”;
sc_exec_sql($sql_upd_members_fields);

copy.jpg