[OT] SQL question

[Mark Schonewille]

Hi Malte,

Here's a simplified example that should work in PostGreSQL:

INSERT INTO table2 SELECT ID,UUID,(MAX(running_number)+1) AS 
first_number FROM table1

I'm not sure whether you want MAX(running_number) for one particular 
UUID or for all UUID's globally. You can do nested SELECTs, which should 
let you get MAX(running_number) for a particular UUID of you want. 
You'll need to do an IF statement, which I didn't include in the 
example, if you want to check whether that a particular value is in 
UUID, but yes it is possible.

You can do it all in one statement, but it is a little complicated. It 
should be much easier to add a trigger to the database that responds to 
inserts of new UUIDs.

--
Best regards,

Mark Schonewille

Economy-x-Talk Consulting and Software Engineering
Homepage: http://economy-x-talk.com
Twitter: http://twitter.com/xtalkprogrammer
KvK: 50277553

Use Color Converter to convert CMYK, RGB, RAL, XYZ, H.Lab and other 
colour spaces. http://www.color-converter.com

Buy my new book "Programming LiveCode for the Real Beginner" 
http://qery.us/3fi

Fill out this survey please
http://livecodebeginner.economy-x-talk.com/survey/

On 11/11/2013 12:00, Malte Brill wrote:
> Hi all,
>
> I am tearing my hair out on an SQL query. Before I lose my marbles I'd better ask here:
>
> given are 2 tables
>
> table1
>
> ID,UUID,first_number
>
>
> table 2
>
> ID,UUID,running_number
> I need to do an INSERT into table 2.
>
> running_number should always be the last inserted running_number +1, if datasets for UUID are already present in table 2, otherwise, if no data is present for UUID,  it should be table1s first_number +1 for the given UUID.
>
> Anybody can help me to put this into a single query? (reason I need it in one query is, that there might be concurring connections to the DB and I want to lock the table first on postGreSQL)
>
> All the best,
>
> Malte