The Heap™ – Consultancy ©®

« first day (3958 days earlier) ← previous day next day → last day (1207 days later) »

McNets

07:46

Morning

3 hours later…

John K. N.

10:20

Morning

2 hours later…

Hannah Vernon

12:28

Morning

Vérace - get VACCINATED NOW

13:02

A chairde - Morning all!

Paul White

13:24

Aaron Bertrand, Matthews, NC

175k 25 372 580

sqlblog.org/2021/11/05/2021-the-year-of-the-exodus

> [Aaron Bertrand] will be joining Andy as a Staff Database Reliability Engineer, hopefully (eventually) filling some pretty big shoes left by Taryn as she heads off to make big waves at Microsoft.

1 hour later…

Philᵀᴹ

14:29

I got rejected for a graduate job at MS 22 years ago, lol.

Paul White

They're a different company these days. They'll hire anyone now 😀

John K. N.

14:46

😂😂

2 hours later…

bbaird

16:26

good morning heap

Vérace - get VACCINATED NOW

16:48

Hi guys - am trying (see fiddle) to do some UPDATEs & DELETEs in PostgreSQL from a CTE - just can't seem to get it working!... have cte1 (derived data from other tables) - do a SELECT on it - get a few records - cte2.

Now, I would like to a) UPDATE the cte1 based on the values in cte2 (set val2 to NULL) and b) DELETE the record - seems simple, but it keeps telling me that cte1 doesn't exist. Probably a schoolboy error - am tired... fiddle...

McNets

@Vérace-getVACCINATEDNOW isnt there a PK?

I think you should use UPDATE - FROM - JOIN using the table instead of the CTE

Vérace - get VACCINATED NOW

@McNets - Here's a UNIQUE constraint. You mean that I can't use CTEs in an UPDATE FROM JOIN...?

@McNets - tried this - obviously today is just not my day!

Yes - SKUULBOYZ ERROR indeed!

McNets

17:14

@Vérace-getVACCINATEDNOW I haven't checked it

WITH cte1 AS
(
  SELECT * FROM t
),
cte2 AS
(
  SELECT v, val1 AS x FROM cte1
  INTERSECT
  SELECT v, val2 FROM cte1
  ORDER BY v, x
)
UPDATE t
SET val2 = NULL
FROM cte2
WHERE
     t.v = cte2.v and t.val1 = cte2.x

Vérace - get VACCINATED NOW

Yes - that did the trick!

Eventually figured it out - bad hair/slow brain day! :-)

McNets

@Vérace-getVACCINATEDNOW I think you can avoid cte1

cte2 AS
(
  SELECT v, val1 AS x FROM t
  INTERSECT
  SELECT v, val2 FROM t
  ORDER BY v, x
)

Vérace - get VACCINATED NOW

17:33

@McNets - thanks for that - but the whole point is that the first cte is derived from other data - it's not a simple table as shown - I really wanted to delete from the CTE rather than a standard table - I thought that CTEs were essentially the same thing? Obviously not!

I'm trying to solve this using standard ARRAY functionality - not with INTARRAY... I've done it using another method, but I'd really like to do it via ARRAYs...

Cade Roux

17:44

It has to be unambiguous what the base table to be deleted is in the CTE

It can only be one table, not both sides of a join, for instance.

At least in SQL Server.

McNets

@Vérace-getVACCINATEDNOW dbfiddle.uk/…

Cade Roux

Should be all the same restrictions as views. Except views you can make an instead of trigger to be able to delete, and that's obviously not possible for a CTE.

McNets

But, there isn'n neither order nor PK

Vérace - get VACCINATED NOW

@McNets - that uses INTARRAY - I'm trying to do it using standard functionality! :-)

McNets

@Vérace-getVACCINATEDNOW either way, there isn't PK, will you add a row_number?

Vérace - get VACCINATED NOW

17:51

Yes - ROW_NUMBER() OVER () AS rn - the v in my t table in the fiddle is the rn from the records in the SO question!

bbaird

18:43

Today was a needed boost - got some positive feedback on some things I've implemented recently and it looks like I'll get to do some greenfield development for a change.

Look out, I make the mistakes now

Vérace - get VACCINATED NOW

19:15

@McNets - did it - didn't have to update or delete from a cte - it was tricky, but I'd appreciate any comments which you might have? Thanks for your input earlier!

ypercubeᵀᴹ

20:03

@PaulWhite super news.

Now we can wait for bluefeet to apply for mod in dba.se, for the switch to be complete ;)

@Vérace-getVACCINATEDNOW feature not implemented (yet ;)

Vérace - get VACCINATED NOW

20:30

@ypercubeᵀᴹ - worked around the issue - hope they include that functionality soon though! :-) Any comments on this would be welcome... So, is @AaronBetrand going to be a (staff) mod here again?

Gulp... postgresqltutorial.com has been suspended - anyone else not able to get the site? It regularly comes up whenever I ask anything PostgreSQL related - tips are normally fairly good - I've used it on more than one occasion to demo a feature...

Cade Roux

20:55

@PaulWhite They do seem to be hiring everyone. I missed the news about Aaron. That's cool.

Paul White

21:21

It was hot off the press

outis

22:07

Is this a good place to request/suggest new tags? Since Oracle has a newer command line client, the MySQL Shell, having a mysql-shell tag would be useful for various reasons, particularly distinguishing questions about the shell as opposed to the old command line client (which, perhaps, could use a mysql-client tag).

That is, assuming questions about use of the command line clients are on topic; the existence of a mysql-workbench tag suggests they would be.

Vérace - get VACCINATED NOW

22:24

@outis - you can type it in and then create it - I'm not sure what your rep has to be, but any half-way decent tag should be accepted! Command-line clients, particularly ones from one of the main vendors, would certainly be on-topic!

outis

I don't have enough rep. It takes 1500, and I have ~230.

Rather, it takes 300 reputation. That's doable.

1 hour later…