OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

[SOLVED] Update multiple records simultaneously

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base
View previous topic :: View next topic  
Author Message
youcanread
General User
General User


Joined: 07 Feb 2012
Posts: 17
Location: Seattle

PostPosted: Tue Feb 28, 2012 10:49 pm    Post subject: [SOLVED] Update multiple records simultaneously Reply with quote

Hi! I'm using Windows 7 / OpenOffice Base 3.3.0. I've searched around but haven't found an answer to my question.

I'm making a database for the bookstore I work at to help them order books.

Ordering and keeping the database updated would be a lot easier if I could update multiple entries simultaneously:
when I do an order, I'd like to update the "date-ordered" fields of all the books at the same time.

My table is like this:

get | Title | date-ordered
--------------------------
2 | Pies | 2/28/2012
2 | Cats | 2/28/2012
1 | Pony | 2/28/2012
_ | Tree |

When I'm ordering 100 books at a time it's annoying to have to go and paste the identical data for "date-ordered" into a hundred rows. So is there a way to change them all at once?

Thanks for any solutions or leads!


Last edited by youcanread on Thu Mar 01, 2012 12:21 pm; edited 3 times in total
Back to top
View user's profile Send private message Visit poster's website
Sliderule
Super User
Super User


Joined: 29 May 2004
Posts: 2499
Location: 3rd Rock From The Sun

PostPosted: Wed Feb 29, 2012 7:17 am    Post subject: Reply with quote

You can set a DEFAULT value for a database table field ( column ). This is done, for example, with the following commands, that are entered from the Menu ( be sure to change your table and field names as explained below ) :

Tools -> SQL...

Code:
-- Change to DEFAULT value of CURRENT_DATE that is computer date
ALTER TABLE "MyTable" ALTER COLUMN "date-ordered" SET DEFAULT CURRENT_DATE;

-- Make it so NULLs are NOT allowed in the Column
ALTER TABLE "MyTable" ALTER COLUMN "date-ordered" SET NOT NULL;


Explanation: You will have to be certain the code table and field names ( between the double quotes "MyTable" and "date-ordered" ) are entered exactly as you defined them in your database, including CASE ( UPPER / Mixed / lower ). For example, "date-ordered" is NOT the same as "Date-Ordered", nor, "DATE-ORDERED". Smile Additionally, the line starting with two dashes are just COMMENTS, not code, to explain the purpose for the following SQL statement.

What this tells the database, is, if you do NOT place assign a value when entering a new record ( SQL INSERT command ) . . . it will put in the current date ( the date defined as the computer date, so, make sure your computer date is accurate, and not 1980-07-01 Smile ) .

Explanation 2: Please see / review the following link, the post by Sliderule: http://www.oooforum.org/forum/viewtopic.phtml?t=117321

I hope this helps, please be sure to let me / us know.

Sliderule

Thanks to add [Solved] in your first post Title ( edit button ) if your issue has been fixed / resolved.
Back to top
View user's profile Send private message
youcanread
General User
General User


Joined: 07 Feb 2012
Posts: 17
Location: Seattle

PostPosted: Wed Feb 29, 2012 2:45 pm    Post subject: Reply with quote

Thanks for such a quick reply Sliderule!

But, hmm, that's not really what I need. I should have clarified: these aren't new records I'm trying to modify. It'll be the same books ordered every few months, so I'd need to change the dates over and over again. Does that make sense? The date the original record was made is irrelevant-- I need to be able to change the date-ordered every time I do an order. And I want to only alter the books in that order, because not all of them would be ordered at the same time.

So is there a way to group all the books that are NOT NULL in the 'get' field and change their 'date-ordered' field to have the same value?

With a spreadsheet it's so easy--you just type it once, copy, select all the fields, and paste--but I can't get it to work in Base. Any ideas?
Back to top
View user's profile Send private message Visit poster's website
Sliderule
Super User
Super User


Joined: 29 May 2004
Posts: 2499
Location: 3rd Rock From The Sun

PostPosted: Wed Feb 29, 2012 3:13 pm    Post subject: Reply with quote

You said:

youcanread wrote:
It'll be the same books ordered every few months, so I'd need to change the dates over and over again. Does that make sense?

No.

youcanread wrote:
So is there a way to group all the books that are NOT NULL in the 'get' field and change their 'date-ordered' field to have the same value?

I am not certain I understand exactly what you want. You can issue a database command, UPDATE , to change the value of a field.

This command would be issued from the Menu; Tools -> SQL...

Code:
UPDATE "MyTable" SET "date-ordered" = '2012-02-28' WHERE "get" IS NOT NULL;


You would have to enter the date in 'YYYY-MM-DD' format . . . surrounded by single quotes . . and . . . the above would change 100% of the records where "get" IS NOT NULL.

BUT, if you really mean where "get" IS NOT NULL, and, "date-ordered" IS NULL . . . it would be written as:

Code:
UPDATE "MyTable" SET "date-ordered" = '2012-02-28' WHERE "get" IS NOT NULL and "date-entered" IS NULL;

If it were me, I would copy a current table you have, and, TEST it with the correct syntax, for determining what you want. Always, have a BACKUP of your OpenOffice Base file. Smile

I hope this helps, please be sure to let me / us know.

Sliderule

Thanks to add [Solved] in your first post Title ( edit button ) if your issue has been fixed / resolved.
Back to top
View user's profile Send private message
youcanread
General User
General User


Joined: 07 Feb 2012
Posts: 17
Location: Seattle

PostPosted: Thu Mar 01, 2012 12:20 am    Post subject: Reply with quote

Thankyou thankyou thankyou! This is exactly what I need!

I just changed it so the date is automatic every time: UPDATE "MyTable" SET "date-ordered" = CURRENT_DATE WHERE "get" IS NOT NULL;

Now... how can I set this as a macro so I can activate it with a push button on a form? I'm making this database for other people to use, and asking people to go into Tools -> SQL every time would be rough.

I'm new at macros so if you'd rather direct me to a good tutorial on macros and buttons that would help.

Orrr, do you know what I'd have to add to this to make it work?
Sub clear
UPDATE "MyTable" SET "date-ordered" = CURRENT_DATE WHERE "get" IS NOT NULL;
End Sub
Back to top
View user's profile Send private message Visit poster's website
youcanread
General User
General User


Joined: 07 Feb 2012
Posts: 17
Location: Seattle

PostPosted: Thu Mar 01, 2012 12:38 am    Post subject: Reply with quote

I mean, I have the button set up to run the macro but when I run what I posted above it highlights "mytable" in the macro and "says BASIC syntax error Expecting ,."

Expecting ,? Comma? I must be missing something.
Back to top
View user's profile Send private message Visit poster's website
Sliderule
Super User
Super User


Joined: 29 May 2004
Posts: 2499
Location: 3rd Rock From The Sun

PostPosted: Thu Mar 01, 2012 7:15 am    Post subject: Reply with quote

Your two posts above are the first time you mention the words form or macro,.

As far as I am concerned, that is a new topic, and, nothing to do with your original post, which I helped you Solve. Therefore, it is time you mark this forum topic as [Solved] per the instructions given to you in each the posts by Sliderule.

Start a separate forum topic if you must, or better yet, search on the forum first.

Sliderule
Back to top
View user's profile Send private message
youcanread
General User
General User


Joined: 07 Feb 2012
Posts: 17
Location: Seattle

PostPosted: Thu Mar 01, 2012 12:24 pm    Post subject: Reply with quote

I did spend an hour searching and couldn't understand any of the posts about it so I thought it would be easier to ask someone who could answer easily. It just seemed too simple for a new topic. But, fine, I can clutter the forum with a new thread if that's easier.
Back to top
View user's profile Send private message Visit poster's website
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group