dcsimg
www.webdeveloper.com
Results 1 to 5 of 5

Thread: UPDATE statement in 'dynamic' attribute table

  1. #1
    Join Date
    Jul 2009
    Location
    Québec
    Posts
    21

    Question UPDATE statement in 'dynamic' attribute table

    Table name : attributes
    Only 3 fields: userid, attributeid, value

    Table before required UPDATE statement :
    userid attributeid value
    1 14 2000-11-29
    1 33 NULL
    1 34 NULL
    2 14 2002-06-30
    2 33 NULL
    2 34 NULL
    ...

    How do I update it to get this result :
    userid attributeid value
    1 14 2000-11-29
    1 33 11 (or November, ...)
    1 34 29
    2 14 2002-06-30
    2 33 06 (or 6, or June, ...)
    2 34 30
    ...

    I would like to be able to update it in 2 different ways:

    1- Partial update (WHERE attribute '14' LIKE '%11-29%') to target just specific records, or
    2- A full database update in a single swing if it's possible

    Thanks in advance, any help will be greatly appreciated.

    Marco

  2. #2
    Join Date
    Sep 2010
    Posts
    160
    Can one assume that all rows with attributeid 33 should have the month and all with 34 should have the day belonging to the complete date for attributeid 14 for each userid?

    Archie

  3. #3
    Join Date
    Jul 2009
    Location
    Québec
    Posts
    21

    Question

    Yes, precisely, that's what I'm looking to accomplish. But I get lost in my 'WHERE' clause since I'm looking to affect 'rows' (attribute 33, and 34) for each userid WHERE attribute 14 is LIKE something. So basically, I want to find :

    WHERE attributeid '14' LIKE '%11-29%' (I know this is not how to write it, but I don't know how to write the where clause properly...)

    and split this information to SET attributeid 33 = 'month' (will hold month info), and attributeid 34 = 'day' (will hold day info) for any given userid like this :

    userid ; attributeid ; value
    1 ; 14 ; 2000-11-29
    1 ; 33 ; 11 (or November...)
    1 ; 34 ; 29
    2 ; 14 ; 2002-06-30
    2 ; 33 ; 06 (or June...)
    2 ; 34 ; 30
    Etc.

    As I wrote in my original post, I would like to have 2 ways of doing it :

    1- in 'targeted' manner (i.e. WHERE attribute 14 LIKE '%11-29%' or LIKE '%-11-%' to target a specific day, or month, etc.)

    2- in a full swing for the whole database at the same time if it is feasible to automatically decorticate the yyyy-mm-dd date format (stored as text) to extract the month and SET it to attribute 33 and day and SET it to attribute 34.

    I really need some help on this one, although it may be more basic than what I've been looking for and trying...

    Thanks in advance,

    Marco

  4. #4
    Join Date
    Sep 2010
    Posts
    160
    Ok. I will take a look at it later today, a bit busy at work atm.

    But I think it should be doable without too much hassle.

    What database and version are you using?

    Archie

  5. #5
    Join Date
    Jul 2009
    Location
    Québec
    Posts
    21
    I'm using mySQL, and I believe it is version 5.0.22, not sure about it but that's the info I see when I access it through Sequel Pro...

    Also, when I think about it, I am not sure it is an UPDATE statement that is required, since most of these records (for attribute 33, and 34) do not exist today, they must be created. I've got a few hundreds that exist though, which I had created using Excel and importing into MySQL. But that's the hassle I want to avoid.

    Thanks in advance.

    Marco

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles