How to combine specific SELECT command with UPDATE - SQL

Question

I need to update the column of some specific tables in my SQL database.

I've got a SELECT command, which selects ALL the right tables but i have no idea how i should combine that with UPDATE (I'm a complete noob).

SELECT
  c.id_customer AS id_customer,
  id_gender,
  firstname,
  lastname,
  c.email AS email,
  birthday,
  date_add,
  c.active AS active,
  c.*,
  a.id_group
FROM prstshp_customer_group a
LEFT JOIN prstshp_customer c
  ON (a.id_customer = c.id_customer)
WHERE 1
AND a.id_group = 4
AND c.deleted != 1
AND c.id_shop IN (1)
ORDER BY id_group ASC

I have to update the column called id_default_group in table prstshp_customer with the value "4", of specific entries, selected across the table prstshp_customer_group table.

Fahmi · Accepted Answer · 2019-04-03 08:07:52Z

1

use update join

update prstshp_customer
join
(
SELECT
  c.id_customer AS id_customer,
  id_gender,
  firstname,
  lastname,
  c.email AS email,
  birthday,
  date_add,
  c.active AS active,
  a.id_group
FROM prstshp_customer_group a
LEFT JOIN prstshp_customer c
  ON (a.id_customer = c.id_customer)
WHERE 1
AND a.id_group = 4
AND c.deleted != 1
AND c.id_shop IN (1)
)B on prstshp_customer.id_customer=B.id_customer
SET id_default_group=4

edited Apr 3, 2019 at 8:07

answered Apr 3, 2019 at 6:52

Fahmi

37.5k5 gold badges26 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Marco Bersnak Over a year ago

Thank you for the fast response! I tried that and i get following errors: Static analysis: 3 errors were found during analysis. An expression was expected. (near "table" at position 7) Unrecognized keyword. (near "table" at position 7) Unexpected token. (near "prstshp_customer" at position 13)

Marco Bersnak Over a year ago

Also tried to remove "table". Then i get "#1060 - Duplicate column name 'id_customer'"

FanoFN Over a year ago

I think its the c.* in SELECT is the reason

Fahmi Over a year ago

@tcadidot0, yes u r right - i didn't notice it - thank u

Fahmi Over a year ago

@MarcoBersnak, you can check now

|

Collectives™ on Stack Overflow

How to combine specific SELECT command with UPDATE - SQL

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related