Announcement

Collapse
No announcement yet.

Urgent Help ...for a mySQL update query

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Urgent Help ...for a mySQL update query

    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.
    Best regards: - Reinhard -

    I use ScriptCase 8 Enterprise Edition, Version 8.(latest)

    Comment


    • #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)

      Comment


      • #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);
        Attached Files

        Comment

        Working...
        X