MySQL setting a variable from a query and using it inside another query

Question

I cannot get the code below to work. I'm trying to set a variable from a query and use it twice in another. The query runs fine if I copy the first query into the location of the variables. Since the first query will always return the same result I would prefer to not run it twice.

        SET @sid = (SELECT status_id
                    FROM user_relationship_status
                    WHERE status = 'LIKE');

        INSERT INTO user_relationships (user_id, target_user_id, status_id)
        VALUES (
          17,
          22,
          @sid
        )
        ON DUPLICATE KEY UPDATE
          status_id = VALUES(@sid)

Also user_id and target_user_id are both primary keys.

In what way does it not work? Are there any error messages?

Darwin von Corax
– Darwin von Corax

2016-03-27 01:38:36 +00:00
Commented Mar 27, 2016 at 1:38 — Darwin von Corax
– Darwin von Corax, Commented Mar 27, 2016 at 1:38
SELECT @sid:=status_id FROM...

Mihai
– Mihai

2016-03-27 01:40:51 +00:00
Commented Mar 27, 2016 at 1:40 — Mihai
– Mihai, Commented Mar 27, 2016 at 1:40

Pரதீப் · Accepted Answer · 2016-03-27 15:44:24Z

1

This is valid syntax in SQL Server, provided sub-query return's only one row.

 SET @sid = (SELECT status_id
                    FROM user_relationship_status
                    WHERE status = 'LIKE');

In Mysql you need to do like this

SELECT @sid:= status_id 
  FROM user_relationship_status
 WHERE status = 'LIKE'

Insert query should be like

I cannot get the code below to work. I'm trying to set a variable from a query and use it twice in another. The query runs fine if I copy the first query into the location of the variables. Since the first query will always return the same result I would prefer to not run it twice.

    SET @sid = (SELECT status_id
                FROM user_relationship_status
                WHERE status = 'LIKE');

    INSERT INTO user_relationships (user_id, target_user_id, status_id)
    VALUES (
      17,
      22,
      @sid
    )
    ON DUPLICATE KEY UPDATE
      status_id = @sid

edited Mar 27, 2016 at 15:44

answered Mar 27, 2016 at 3:10

Pரதீப்

94.1k21 gold badges144 silver badges179 bronze badges

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

2 Comments

ovg Over a year ago

I get: INSERT INTO user_relationships (user_id, target_user_id, status_id) VALUES ( 17, 18, @sid ) ON DUPLICATE KEY UPDATE status_id = VALUES(@sid) Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@sid)' at line 8 0.000 sec

Pரதீப் Over a year ago

@ovg - Updated my answer

Raka · Accepted Answer · 2016-03-27 08:40:48Z

0

Use below script to set @sid

SELECT status_id INTO @sid FROM user_relationship_status WHERE status = 'LIKE' limit 1;

answered Mar 27, 2016 at 8:40

Raka

4272 silver badges10 bronze badges

2 Comments

ovg Over a year ago

This also works for variables but I'm still having trouble with the insert part..

Raka Over a year ago

For error code 1452: users table does not any entry for user_id 18.

Collectives™ on Stack Overflow

MySQL setting a variable from a query and using it inside another query

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related