¿Cómo ejecutar sentencias UPDATE y DELETE?

Les comparto mis querys:

SELECT salesid, pricepaid, commission
FROM sales s
WHERE eventid IN (
	SELECT eventid
	FROM event e
	WHERE eventname = 'Beck'
);

CREATE table sales_auxiliar AS (
	SELECT salesid, pricepaid * 2 pricepaid , commission * 2 commission 
	FROM sales s
	WHERE eventid IN (
		SELECT eventid
		FROM event e
		WHERE eventname = 'Beck'
	)
);

UPDATE sales
SET pricepaid =sa.pricepaid
FROM sales_auxiliar sa
WHERE sales.salesid = sa.salesid;

DELETE FROM sales
USING sales_auxiliar
WHERE sales.salesid = sales_auxiliar.salesid;


DROP TABLE sales_auxiliar;

CREATE table sales_auxiliar AS (
	SELECT *
	FROM sales s
	WHERE eventid IN (
		SELECT eventid
		FROM event e
		WHERE eventname = 'Macbeth'
	)
);

INSERT INTO sales (SELECT * FROM sales_auxiliar);

Para hacer update o delete en Redshift, la mejor practica es crear una tabla auxiliar con todos los datos involucrados en el borrado o actualización, para después hacer utilizar esa nueva tabla para hacer una sola operación cruzada con la tabla original.

1. cargar en tabla auxiliar los datos actualizados
2. borrar los datos de la tabla oficial, con ayuda de la tabla auxiliar (uso por ejemplo del id)
3. Insertar en la tabla oficial los datos de la tabla auxiliar

SELECT * FROM sales LIMIT 10;

SELECT *
FROM sales s 
WHERE eventid ;

SELECT *
FROM event e 
WHERE eventname  = 'Beck';

SELECT eventid 
FROM event e 
WHERE eventname  = 'Beck';

SELECT *
FROM sales s 
WHERE eventid in(
	SELECT eventid 
	FROM event e 
	WHERE eventname  = 'Beck'
) ;

DROP TABLE sales_auxiliar;


CREATE TABLE sales_auxiliar AS (
	SELECT salesid, pricepaid  * 2 pricepaid, commission * 2 commission 
	FROM sales s 
	WHERE eventid in(
		SELECT eventid 
		FROM event e 
		WHERE eventname  = 'Beck'
)
);

SELECT * 
FROM sales_auxiliar
LIMIT 10;

SELECT * 
FROM sales_auxiliar
WHERE salesid = 137519
LIMIT 10; --448

SELECT * 
FROM sales
WHERE salesid = 137519
LIMIT 10; --448

UPDATE sales 
SET pricepaid = sa.pricepaid
FROM sales_auxiliar sa
WHERE sales.salesid = sa.salesid;

DELETE FROM sales 
USING sales_auxiliar
WHERE sales.salesid = sales_auxiliar.salesid;

DROP TABLE sales_auxiliar;

CREATE TABLE sales_auxiliar AS (
	SELECT * 
	FROM sales s 
	WHERE eventid in(
		SELECT eventid 
		FROM event e 
		WHERE eventname  = 'Macbeth'));
	
DELETE FROM sales 
USING sales_auxiliar
WHERE sales.salesid = sales_auxiliar.salesid;

INSERT INTO sales (SELECT * FROM sales_auxiliar);

SELECT eventid 
FROM event e 
WHERE eventname  = 'Macbeth';

SELECT * 
	FROM sales s 
	WHERE eventid in(
		SELECT eventid 
		FROM event e 
		WHERE eventname  = 'Macbeth');

SELECT * 
FROM sales_auxiliar
WHERE salesid = 107121
LIMIT 10; --82, --12.3

SELECT * 
FROM sales
WHERE salesid = 107121
LIMIT 10; --82, --12.3

Excelente