Обсуждение: Update from same table

Поиск

Список

Период

Сортировка

Update from same table

От

Jürgen Cappel

Дата:

04 февраля 2004 г., 12:47:04

Hello,

I want to update columns in a table that match a fixed key
from the same column of the same table matching another fixed key.
There can be several tuples per key distinguished by a secondary id.
Tuples are unique with the combined keys. Maybe a query could look
something like this:
UPDATE a.mytable from b.mytableSET a.mycolumn = b.mycolumnWHERE a.firstid = some_keyAND b.firstid = some_other_keyAND
a.secondaryid= b.secondaryid;
 

But it's a syntax error. I tried a subselect but i'm failing to connect
the subselect's 2nd ID with the update's 2nd ID.  Any help ?  Thanks,

Jürgen

Re: Update from same table

От

Josh Berkus

Дата:

04 февраля 2004 г., 13:29:20

Jurgen,

>     UPDATE a.mytable from b.mytable
>     SET a.mycolumn = b.mycolumn
>     WHERE a.firstid = some_key
>     AND b.firstid = some_other_key
>     AND a.secondaryid = b.secondaryid;

Very close, actually; you just need to fix the table alias:
    UPDATE mytable FROM mytable as b    SET mytable.mycolumn = b.mycolumnWHERE mytable.firstid = some_key    AND
b.firstid= some_other_key    AND mytable.secondaryid = b.secondaryid;
 

AFAIK, one can't alias a table name in an UPDATE clause.  So for that instance 
of the table, you need to use the full name.

-- 
-Josh BerkusAglio Database SolutionsSan Francisco

TIME ZONE SQL

От

"Raman"

Дата:

04 февраля 2004 г., 14:01:46

Hi Pls see this Query

I have following fields in my table "customer_events"

a) time_difference (which has values like -05:00 , +05:30, +00:00 etc)
b) start_time (has value like 11:05, 10:00 etc)
c) send_before_time (has value like 00:05, 00:10 etc)

select 
((CURRENT_TIME(0) AT TIME ZONE "interval" (time_difference)) BETWEEN 
(start_time::time - send_before_time::time) 
and start_time::time) as yesno,
current_time(0), 
CURRENT_TIME(0) AT TIME ZONE "interval" (time_difference), 
(start_time::time - send_before_time::time) as difference, 
time_difference
from 
customer_events


MY PROBLEM:

This query runs fine when i have 
time_difference value like +5:30 +5:00 i.e. works fine for positive values
but failes for negative values i.e. -7:00, -6:00 etc

I don't know WHY WHY... pls help
I am helpless.

do reply back....

Regards,
Raman Garg

Re: TIME ZONE SQL

От

Richard Huxton

Дата:

04 февраля 2004 г., 16:03:22

On Wednesday 04 February 2004 17:57, Raman wrote:
>
> This query runs fine when i have
> time_difference value like +5:30 +5:00 i.e. works fine for positive values
> but failes for negative values i.e. -7:00, -6:00 etc
>
> I don't know WHY WHY... pls help
> I am helpless.

Can you give example outputs? It's difficult to decide otherwise.
--  Richard Huxton Archonet Ltd

Re: TIME ZONE SQL

От

"Raman"

Дата:

05 февраля 2004 г., 04:32:24

Hi Richard,
Follwing are the Results that I get

Lets say I am in IST (Indian standart time) 15:00:00 hrs
so equivalent time at US Mountain (-7:00) is 02:30:00 hrs
and equivalent time at Japan(+9:00) is 18:30:00hrs

NOW WHAT I have is this

I have following fields in my table "customer_events"

a) time_difference (which has values like +09:00 , -7:00, +00:00 etc)
b) start_time (has value like 11:00:00 , 10:00:00 etc)
c) send_before_time (has value like 00:15:00 , 00:07:00 etc)

select
((CURRENT_TIME(0) AT TIME ZONE "interval" (time_difference)) BETWEEN
(start_time::time - send_before_time::time)
and start_time::time) as yesno,
current_time(0),
CURRENT_TIME(0) AT TIME ZONE "interval" (time_difference),
(start_time::time - send_before_time::time) as difference,
time_difference
from
customer_events

WHEN I run "between" query like

((CURRENT_TIME(0) AT TIME ZONE "interval" (time_difference)) BETWEEN
(start_time::time - send_before_time::time)
and start_time::time)

it returns True (YES) when  time_difference value are like +5:30 +5:00 i.e.
works fine for positive values
but failes for negative values i.e. -7:00, -6:00 time Zone values and
returns me FALSE.

So as per upper example
lets say for an Japanese event "start_time=18:34:00" and
"send_before_time="00:05:00" my above "between" query return true as
current_time at japan zone (18:30) lies between thtat

but for US Mountain event at "start_time=02:34:00"  and
"send_before_time=00:05:00"  above "between" query return FALSE and
current_time at US mountain zone (02:30) lies between that

I don't know WHY

Pls help.. in this. Also pls let me know if you need any other information.


With Regards,
Raman Garg











-- Raman
----- Original Message ----- 
From: "Richard Huxton" <dev@archonet.com>
To: "Raman" <ramang@smartdatainc.com>; "pgsql-sql"
<pgsql-sql@postgresql.org>
Sent: Thursday, February 05, 2004 1:33 AM
Subject: Re: [SQL] TIME ZONE SQL


> On Wednesday 04 February 2004 17:57, Raman wrote:
> >
> > This query runs fine when i have
> > time_difference value like +5:30 +5:00 i.e. works fine for positive
values
> > but failes for negative values i.e. -7:00, -6:00 etc
> >
> > I don't know WHY WHY... pls help
> > I am helpless.
>
> Can you give example outputs? It's difficult to decide otherwise.
> -- 
>   Richard Huxton
>   Archonet Ltd
>

Re: TIME ZONE SQL

От

Richard Huxton

Дата:

05 февраля 2004 г., 07:01:15

On Thursday 05 February 2004 08:28, Raman wrote:
> Hi Richard,
> Follwing are the Results that I get

> WHEN I run "between" query like
>
> ((CURRENT_TIME(0) AT TIME ZONE "interval" (time_difference)) BETWEEN
> (start_time::time - send_before_time::time)
> and start_time::time)

I think the issue is the "send_before_time" - I think this should be an 
interval rather than a time. I'm assuming it means something like "send 
warning message X hours before ..."

Using the SQL below (your test data might need different values):

CREATE TABLE tztest (   id serial,   time_difference   interval,   start_time        time,   send_before_time  time,
PRIMARYKEY (id)
 
);

COPY tztest(time_difference,start_time,send_before_time) FROM stdin;
-03 01:00   00:45
-03 02:00   00:45
-03 03:00   00:45
-03 04:00   00:45
-03 05:00   00:45
-03 06:00   00:45
-03 07:00   00:45
-03 08:00   00:45
-03 09:00   00:45
-03 10:00   00:45
-03 11:00   00:45
-03 12:00   00:45
-03 13:00   00:45
-03 14:00   00:45
-03 15:00   00:45
-03 16:00   00:45
-03 17:00   00:45
-03 18:00   00:45
-03 19:00   00:45
-03 20:00   00:45
-03 21:00   00:45
\.


select
((CURRENT_TIME(0) AT TIME ZONE "interval" (time_difference)) BETWEEN
(start_time::time - send_before_time::time)
and start_time::time) as yesno,

current_time(0) AS curr_tm,
CURRENT_TIME(0) AT TIME ZONE "interval" (time_difference) AS 
curr_with_timediff,

(start_time::time - send_before_time::time) as start_pt,
start_time AS end_pt,
time_difference
from
tztest;

-- Notice how we use send_before_time as an interval here
--
select
((CURRENT_TIME(0) AT TIME ZONE "interval" (time_difference)) BETWEEN
(start_time::time - send_before_time::interval)
and start_time::time) as yesno,

current_time(0) AS curr_tm,
CURRENT_TIME(0) AT TIME ZONE "interval" (time_difference) AS 
curr_with_timediff,

(start_time::time - send_before_time::interval) as start_pt,
start_time AS end_pt,
time_difference
from
tztest;

-- END SQL --

Gives the following results:
richardh=# \i timezone_test.sqlyesno |   curr_tm   | curr_with_timediff | start_pt |  end_pt  | 
time_difference
-------+-------------+--------------------+----------+----------+-----------------f     | 10:54:29+00 | 07:54:29-03
  | 00:15    | 01:00:00 | -03:00f     | 10:54:29+00 | 07:54:29-03        | 01:15    | 02:00:00 | -03:00f     |
10:54:29+00| 07:54:29-03        | 02:15    | 03:00:00 | -03:00
 
...etc...f     | 10:54:29+00 | 07:54:29-03        | 20:15    | 21:00:00 | -03:00
(21 rows)
yesno |   curr_tm   | curr_with_timediff | start_pt |  end_pt  | 
time_difference
-------+-------------+--------------------+----------+----------+-----------------f     | 10:54:29+00 | 07:54:29-03
  | 00:15:00 | 01:00:00 | -03:00
 
...etc...f     | 10:54:29+00 | 07:54:29-03        | 09:15:00 | 10:00:00 | -03:00t     | 10:54:29+00 | 07:54:29-03
| 10:15:00 | 11:00:00 | -03:00f     | 10:54:29+00 | 07:54:29-03        | 11:15:00 | 12:00:00 | -03:00
 
...etc...f     | 10:54:29+00 | 07:54:29-03        | 20:15:00 | 21:00:00 | -03:00
(21 rows)

Notice the difference between start_pt in each case. In the first example, 
time - time = difference, wheras in the second time - difference = time

Does that help out at your end?
--  Richard Huxton Archonet Ltd

Re: TIME ZONE SQL

От

"Raman Garg"

Дата:

05 февраля 2004 г., 11:22:53

Hi Richard,

What I am having is

CREATE TABLE "customer_events" (
"event_id" numeric (10) NOT NULL,
"customer_id" numeric (10) NOT NULL,
"event_name" varchar (100) ,
"event_datetime" date ,
"start_time" time ,
"repeat_untill_date" date ,
"send_before_time" time,"time_difference" time
PRIMARY KEY ("event_id"))

So my "send_before_time" is of datatype "time" only as you have assumed.
only difference was, In your table you are having time_difference field as
"interval" type.

I am subtracting time field from a time field. i.e. start_time::time -
send_before_time::time
so creating (start_time::time - send_before_time::time) as start_pt is not
making any difference at my side.
(I hope I am getting your point what you have explained)

Anyways i got a solution in this way..
Actually my "between" is creating some problems and is not giving me results
so what I have done is . IN MY WHERE CLAUSE OF QUERY:

Where  ((CURRENT_TIME(0) AT TIME ZONE "interval" (time_difference)) BETWEEN(start_time::time -
send_before_time::time)andstart_time::time)OR((CURRENT_TIME(0) AT TIME ZONE "interval" (time_difference)) BETWEEN
 
(start_time::time and  (start_time::time - send_before_time::time))

--> check the difference two between
now one of my results work for positive time zone(+5:30) and another for
negative time zone(-7:00)

Well, it worked for me now.. maybe some logic of neagative time zone is
there due to which our time calculation make the difference of two time
greater. :-?

Thanks for your descriptive and nice explanation...

Regards,
-- Raman


----- Original Message ----- 
From: "Richard Huxton" <dev@archonet.com>
To: "Raman" <ramang@smartdatainc.com>; "pgsql-sql"
<pgsql-sql@postgresql.org>
Sent: Thursday, February 05, 2004 4:30 PM
Subject: Re: [SQL] TIME ZONE SQL


> On Thursday 05 February 2004 08:28, Raman wrote:
> > Hi Richard,
> > Follwing are the Results that I get
>
> > WHEN I run "between" query like
> >
> > ((CURRENT_TIME(0) AT TIME ZONE "interval" (time_difference)) BETWEEN
> > (start_time::time - send_before_time::time)
> > and start_time::time)
>
> I think the issue is the "send_before_time" - I think this should be an
> interval rather than a time. I'm assuming it means something like "send
> warning message X hours before ..."
>
> Using the SQL below (your test data might need different values):
>
> CREATE TABLE tztest (
>     id serial,
>     time_difference   interval,
>     start_time        time,
>     send_before_time  time,
>     PRIMARY KEY (id)
> );
>
> COPY tztest(time_difference,start_time,send_before_time) FROM stdin;
> -03 01:00   00:45
> -03 02:00   00:45
> -03 03:00   00:45
> -03 04:00   00:45
> -03 05:00   00:45
> -03 06:00   00:45
> -03 07:00   00:45
> -03 08:00   00:45
> -03 09:00   00:45
> -03 10:00   00:45
> -03 11:00   00:45
> -03 12:00   00:45
> -03 13:00   00:45
> -03 14:00   00:45
> -03 15:00   00:45
> -03 16:00   00:45
> -03 17:00   00:45
> -03 18:00   00:45
> -03 19:00   00:45
> -03 20:00   00:45
> -03 21:00   00:45
> \.
>
>
> select
> ((CURRENT_TIME(0) AT TIME ZONE "interval" (time_difference)) BETWEEN
> (start_time::time - send_before_time::time)
> and start_time::time) as yesno,
>
> current_time(0) AS curr_tm,
> CURRENT_TIME(0) AT TIME ZONE "interval" (time_difference) AS
> curr_with_timediff,
>
> (start_time::time - send_before_time::time) as start_pt,
> start_time AS end_pt,
> time_difference
> from
> tztest;
>
> -- Notice how we use send_before_time as an interval here
> --
> select
> ((CURRENT_TIME(0) AT TIME ZONE "interval" (time_difference)) BETWEEN
> (start_time::time - send_before_time::interval)
> and start_time::time) as yesno,
>
> current_time(0) AS curr_tm,
> CURRENT_TIME(0) AT TIME ZONE "interval" (time_difference) AS
> curr_with_timediff,
>
> (start_time::time - send_before_time::interval) as start_pt,
> start_time AS end_pt,
> time_difference
> from
> tztest;
>
> -- END SQL --
>
> Gives the following results:
> richardh=# \i timezone_test.sql
>  yesno |   curr_tm   | curr_with_timediff | start_pt |  end_pt  |
> time_difference
> -------+-------------+--------------------+----------+----------+---------
--------
>  f     | 10:54:29+00 | 07:54:29-03        | 00:15    | 01:00:00 | -03:00
>  f     | 10:54:29+00 | 07:54:29-03        | 01:15    | 02:00:00 | -03:00
>  f     | 10:54:29+00 | 07:54:29-03        | 02:15    | 03:00:00 | -03:00
> ...etc...
>  f     | 10:54:29+00 | 07:54:29-03        | 20:15    | 21:00:00 | -03:00
> (21 rows)
>
>  yesno |   curr_tm   | curr_with_timediff | start_pt |  end_pt  |
> time_difference
> -------+-------------+--------------------+----------+----------+---------
--------
>  f     | 10:54:29+00 | 07:54:29-03        | 00:15:00 | 01:00:00 | -03:00
> ...etc...
>  f     | 10:54:29+00 | 07:54:29-03        | 09:15:00 | 10:00:00 | -03:00
>  t     | 10:54:29+00 | 07:54:29-03        | 10:15:00 | 11:00:00 | -03:00
>  f     | 10:54:29+00 | 07:54:29-03        | 11:15:00 | 12:00:00 | -03:00
> ...etc...
>  f     | 10:54:29+00 | 07:54:29-03        | 20:15:00 | 21:00:00 | -03:00
> (21 rows)
>
> Notice the difference between start_pt in each case. In the first example,
> time - time = difference, wheras in the second time - difference = time
>
> Does that help out at your end?
> -- 
>   Richard Huxton
>   Archonet Ltd
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
>

Re: TIME ZONE SQL

От

Richard Huxton

Дата:

05 февраля 2004 г., 12:32:11

On Thursday 05 February 2004 14:59, Raman Garg wrote:
> Hi Richard,
>
> What I am having is
>
> CREATE TABLE "customer_events" (
> "event_id" numeric (10) NOT NULL,
> "customer_id" numeric (10) NOT NULL,
> "event_name" varchar (100) ,
> "event_datetime" date ,
> "start_time" time ,
> "repeat_untill_date" date ,
> "send_before_time" time,
>  "time_difference" time
> PRIMARY KEY ("event_id"))
>
> So my "send_before_time" is of datatype "time" only as you have assumed.
> only difference was, In your table you are having time_difference field as
> "interval" type.
>
> I am subtracting time field from a time field. i.e. start_time::time -
> send_before_time::time
> so creating (start_time::time - send_before_time::time) as start_pt is not
> making any difference at my side.
> (I hope I am getting your point what you have explained)

My point is that subtracting one time from another does not give you a time. 3:30pm (time) - 2:00pm (time) = 1h30m
(interval)

If "send_before_time" is an interval (e.g. '1 hour and 30 minutes') you should 
probably make it an interval.
If it is in fact a time (e.g. '2:00 pm') then it doesn't make sense to 
subtract it.

Even if your solution works, I'd recommend getting your types right - it'll 
save effort later.
--  Richard Huxton Archonet Ltd

Re: TIME ZONE SQL

От

Tom Lane

Дата:

05 февраля 2004 г., 12:55:08

"Raman Garg" <ramang@smartdatainc.com> writes:
> Actually my "between" is creating some problems and is not giving me results
> so what I have done is . IN MY WHERE CLAUSE OF QUERY:

I suspect that this revised clause will give you problems too, namely
selecting rows you don't want.

I think what may actually be happening here is that you have times
wrapping around past midnight.  For instance consider

regression=# select '10:30'::time + '15 hours'::interval;?column?
----------01:30:00
(1 row)

regression=# select '10:30'::time - '15 hours'::interval;?column?
----------19:30:00
(1 row)

It seems to me that your approach to the problem is all wrong, and you
need to be using timestamp-based calculations not time-of-day-based
calculations.
        regards, tom lane

Вход в личный кабинет

Восстановление пароля

Подтверждение аккаунта

Изменение пароля

Обсуждение: Update from same table